1: internal static class SQLBuilder
2: {
3: public static void CreateStoredProcedures(string ConnectionString, Class Class, ClassManager Manager, bool RecreateIfExists)
4: {
5: CreateInsertStoredProcedures(ConnectionString, Class, Manager);
6: CreateUpdateStoredProcedures(ConnectionString, Class, Manager);
7: CreateDeleteStoredProcedures(ConnectionString, Class, Manager);
8: CreateSelectStoredProcedures(ConnectionString, Class, Manager);
9: }
10:
11: private static void CreateSelectStoredProcedures(string ConnectionString, Class Class, ClassManager Manager)
12: {
13: bool Exists = false;
14: string ClassName = Class.OriginalType.Name;
15: Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("Select * from sys.Procedures where name=@ProcedureName", ConnectionString, CommandType.Text);
16: if (!Exists)
17: {
18: Select SelectStoredProcedures = new Select(Class, Manager);
19: Helper.Command = SelectStoredProcedures.ToString();
20: try
21: {
22: Helper.Open();
23: Helper.ExecuteNonQuery();
24: }
25: catch { }
26: finally { Helper.Close(); }
27: }
28: }
29:
30: private static void CreateDeleteStoredProcedures(string ConnectionString, Class Class, ClassManager Manager)
31: {
32: bool Exists = false;
33: string ClassName = Class.OriginalType.Name;
34: Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("Select * from sys.Procedures where name=@ProcedureName", ConnectionString, CommandType.Text);
35: if (!Exists)
36: {
37: Delete DeleteStoredProcedures = new Delete(Class, Manager);
38: Helper.Command = DeleteStoredProcedures.ToString();
39: try
40: {
41: Helper.Open();
42: Helper.ExecuteNonQuery();
43: }
44: catch { }
45: finally { Helper.Close(); }
46: }
47: }
48:
49: private static void CreateUpdateStoredProcedures(string ConnectionString, Class Class, ClassManager Manager)
50: {
51: bool Exists = false;
52: string ClassName = Class.OriginalType.Name;
53: Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("Select * from sys.Procedures where name=@ProcedureName", ConnectionString, CommandType.Text);
54: if (!Exists)
55: {
56: Update UpdateStoredProcedures = new Update(Class, Manager);
57: Helper.Command = UpdateStoredProcedures.ToString();
58: try
59: {
60: Helper.Open();
61: Helper.ExecuteNonQuery();
62: }
63: catch { }
64: finally { Helper.Close(); }
65: }
66: }
67:
68: private static void CreateInsertStoredProcedures(string ConnectionString, Class Class, ClassManager Manager)
69: {
70: bool Exists = false;
71: string ClassName = Class.OriginalType.Name;
72: Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("Select * from sys.Procedures where name=@ProcedureName", ConnectionString, CommandType.Text);
73: if (!Exists)
74: {
75: Insert InsertStoredProcedures = new Insert(Class, Manager);
76: Helper.Command = InsertStoredProcedures.ToString();
77: try
78: {
79: Helper.Open();
80: Helper.ExecuteNonQuery();
81: }
82: catch { }
83: finally { Helper.Close(); }
84: }
85: }
86:
87: public static void CreateTable(string ConnectionString, Class Class, ClassManager Manager, bool RecreateIfExists)
88: {
89: bool Exists = false;
90: string ClassName = Class.OriginalType.Name;
91: Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("Select * from sys.Tables where name=@TableID", ConnectionString, CommandType.Text);
92: if (!RecreateIfExists)
93: {
94: try
95: {
96: Helper.Open();
97: Helper.AddParameter("@TableID", ClassName, 200);
98: Helper.ExecuteReader();
99: if (Helper.Read())
100: {
101: Exists = true;
102: }
103: }
104: catch { }
105: finally { Helper.Close(); }
106: }
107: if (!Exists)
108: {
109: CreateTable Table = new CreateTable(Class, Manager);
110: Helper.Command = Table.ToString();
111: try
112: {
113: Helper.Open();
114: Helper.ExecuteNonQuery();
115: }
116: catch { }
117: finally { Helper.Close(); }
118: }
119: }
120:
121: public static void CreateDatabase(string ConnectionString, string DatabaseName, bool RecreateIfExists)
122: {
123: bool Exists = false;
124: Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("SELECT * FROM Master.sys.Databases where name=@DatabaseID", ConnectionString, CommandType.Text);
125: if (!RecreateIfExists)
126: {
127: try
128: {
129: Helper.Open();
130: Helper.AddParameter("@DatabaseID", DatabaseName, 100);
131: Helper.ExecuteReader();
132: if (Helper.Read())
133: {
134: Exists = true;
135: }
136: }
137: catch { }
138: finally { Helper.Close(); }
139: }
140: if (!Exists)
141: {
142: Helper.Command = "CREATE DATABASE " + DatabaseName.Replace("'", "");
143: try
144: {
145: Helper.Open();
146: Helper.ExecuteNonQuery();
147: }
148: catch { }
149: finally { Helper.Close(); }
150: }
151: }
152:
153: internal static bool Save(string ConnectionString, object Object, Class ObjectDefinition, ClassManager ClassManager)
154: {
155: Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("", ConnectionString, CommandType.Text);
156: Insert ObjectInsert = new Insert(ObjectDefinition, ClassManager);
157: ObjectInsert.Run(Object, Helper);
158: return false;
159: }
160:
161: public static object SelectByID(string ConnectionString, object IDValue, Class Class, ClassManager Manager)
162: {
163: Utilities.SQLHelper.SQLHelper Helper = new Utilities.SQLHelper.SQLHelper("", ConnectionString, CommandType.Text);
164: Select TempSelect=new Select(Class,Manager);
165: return TempSelect.SelectByID(IDValue,Helper);
166: }
167: }
168:
169: internal class Insert:IStatement
170: {
171: public override string ToString()
172: {
173: StringBuilder Builder = new StringBuilder("EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo." + _Class.OriginalType.Name + "_Insert\n");
174: StringBuilder ListBuilder = new StringBuilder();
175: string Splitter = "";
176: string Splitter2="";
177: StringBuilder Values=new StringBuilder();
178: StringBuilder Parameters=new StringBuilder();
179: foreach (IDataType Column in Columns)
180: {
181: if (Column != null && !Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
182: {
183: if (!string.IsNullOrEmpty(Column.CreateStoredProcedureParameter(false, true)))
184: {
185: Builder.Append(Splitter + Column.CreateStoredProcedureParameter(false, true));
186: Values.Append(Splitter2 + Column.Name);
187: Parameters.Append(Splitter2 + "@" + Column.Name);
188: Splitter = ",\n";
189: Splitter2 = ",";
190: }
191: }
192: else if (Column != null && Column.Type.Equals("List", StringComparison.CurrentCultureIgnoreCase))
193: {
194: ListBuilder.Append(" " + Column.CreateInsertCommand());
195: }
196: }
197:
198: Builder.Append("\nAS\nINSERT INTO " + _Class.OriginalType.Name + "(" + Values.ToString() + ") VALUES (" + Parameters.ToString() + ")\nSELECT scope_identity() as [ID]\nRETURN'\n");
199: return Builder.ToString()+ListBuilder.ToString();
200: }
201: }