1: using System;
2: using System.Configuration;
3: using System.Data;
4: using System.Data.SqlClient;
5:
6: namespace Ia.Cl.Model.Db
7: {
8: ////////////////////////////////////////////////////////////////////////////
9:
10: /// <summary publish="true">
11: /// SQL Server support class.
12: /// </summary>
13: /// <remarks>
14: /// Copyright © 2001-2020 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
15: ///
16: /// This library is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
17: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
18: ///
19: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
20: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
21: ///
22: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
23: ///
24: /// Copyright notice: This notice may not be removed or altered from any source distribution.
25: /// </remarks>
26: public class SqlServer
27: {
28: private string connectionString;
29:
30: ////////////////////////////////////////////////////////////////////////////
31:
32: /// <summary>
33: ///
34: /// </summary>
35: public SqlServer()
36: {
37: connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
38: }
39:
40: ////////////////////////////////////////////////////////////////////////////
41:
42: /// <summary>
43: ///
44: /// </summary>
45: public SqlServer(string _connectionString)
46: {
47: connectionString = _connectionString;
48: }
49:
50: ////////////////////////////////////////////////////////////////////////////
51:
52: /// <summary>
53: ///
54: /// </summary>
55: public void Sql(string sql)
56: {
57: using (SqlConnection sqlConnection = new SqlConnection(connectionString))
58: {
59: var sqlCommand = new SqlCommand();
60:
61: sqlCommand.CommandType = CommandType.Text; // default
62: sqlCommand.CommandText = sql;
63: sqlCommand.Connection = sqlConnection;
64:
65: sqlConnection.Open();
66:
67: sqlCommand.ExecuteNonQuery();
68: sqlConnection.Close();
69: }
70: }
71:
72: ////////////////////////////////////////////////////////////////////////////
73:
74: /// <summary>
75: ///
76: /// </summary>
77: public DataTable Select(string sql)
78: {
79: DataSet dataSet = new DataSet();
80: DataTable dataTable = new DataTable();
81: SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
82:
83: using (SqlConnection sqlConnection = new SqlConnection(connectionString))
84: {
85: var sqlCommand = new SqlCommand(sql, sqlConnection);
86:
87: sqlConnection.Open();
88:
89: sqlDataAdapter.SelectCommand = sqlCommand;
90:
91: sqlDataAdapter.Fill(dataSet);
92:
93: //sc.Close();
94:
95: dataTable = dataSet.Tables[0];
96: }
97:
98: return dataTable;
99: }
100:
101: ////////////////////////////////////////////////////////////////////////////
102:
103: /// <summary>
104: /// Truncate a table, completely deleting it
105: /// </summary>
106: public void Truncate(string dataTableName)
107: {
108: Sql("truncate table " + dataTableName);
109: }
110:
111: ////////////////////////////////////////////////////////////////////////////
112:
113: /// <summary>
114: ///
115: /// </summary>
116: public void SqlBulkCopy(DataTable dataTable, out Ia.Cl.Model.Result result)
117: {
118: using (SqlConnection sqlConnection = new SqlConnection(connectionString))
119: {
120: sqlConnection.Open();
121:
122: // Note that the column positions in the source DataTable match the column positions in the destination table so there is no need to map columns.
123: using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection))
124: {
125: result = new Ia.Cl.Model.Result();
126:
127: if (dataTable != null && dataTable.Rows.Count > 0)
128: {
129: // DestinationTableName is a three-part name (<database>.<owningschema>.<name>). You can qualify the table name with its database and owning schema if you choose. However, if the table name uses an underscore ("_") or any other special characters, you must escape the name using surrounding brackets as in ([<database>.<owningschema>.<name_01>]).
130: // see: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy.destinationtablename?view=dotnet-plat-ext-7.0
131: //bulkCopy.DestinationTableName = "[Ia_TentPlay.dbo." + dataTable.TableName + "]";
132: bulkCopy.DestinationTableName = "[" + dataTable.TableName + "]";
133:
134: try
135: {
136: bulkCopy.WriteToServer(dataTable);
137:
138: result.AddSuccess("DataTable copied: " + dataTable.TableName + " had " + dataTable.Rows.Count + " record(s). ");
139: }
140: catch (Exception ex)
141: {
142: result.AddError(ex.Message);
143: }
144: }
145: else
146: {
147: result.AddWarning("DataTable " + dataTable.TableName + " is null or row count is zero. ");
148: }
149: }
150: }
151: }
152:
153: ////////////////////////////////////////////////////////////////////////////
154:
155: /// <summary>
156: /// Return a DataTable of properties of tables that start with "ia_"
157: /// </summary>
158: public DataTable Schema()
159: {
160: var dataTable = Select("SELECT TABLE_NAME + '_' + COLUMN_NAME AS id, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME LIKE 'ia_%')");
161:
162: var dataColumns = new DataColumn[1];
163:
164: dataColumns[0] = dataTable.Columns["id"];
165:
166: dataTable.PrimaryKey = dataColumns;
167:
168: return dataTable;
169: }
170:
171: ////////////////////////////////////////////////////////////////////////////
172:
173: /// <summary>
174: ///
175: /// </summary>
176: public string Scalar(string sql)
177: {
178: string s;
179: SqlCommand sqlCommand;
180:
181: using (SqlConnection sqlConnection = new SqlConnection(connectionString))
182: {
183: sqlCommand = new SqlCommand(sql, sqlConnection);
184:
185: sqlConnection.Open();
186:
187: try
188: {
189: s = sqlCommand.ExecuteScalar().ToString();
190: }
191: catch
192: {
193: s = null;
194: }
195:
196: //sc.Close();
197: }
198:
199: return s;
200: }
201:
202: ////////////////////////////////////////////////////////////////////////////
203:
204: /// <summary>
205: ///
206: /// </summary>
207: public int ScalarInteger(string sql)
208: {
209: // return a DataTable of result rows
210: int n;
211: SqlCommand sqlCommand;
212:
213: using (SqlConnection sqlConnection = new SqlConnection(connectionString))
214: {
215: sqlCommand = new SqlCommand(sql, sqlConnection);
216:
217: sqlConnection.Open();
218:
219: try
220: {
221: n = (System.Int32)sqlCommand.ExecuteScalar();
222: }
223: catch (Exception)
224: {
225: n = 0;
226: }
227:
228: //sc.Close();
229: }
230:
231: return n;
232: }
233:
234: ////////////////////////////////////////////////////////////////////////////
235:
236: /// <summary>
237: ///
238: /// </summary>
239: public int ScalarSmallInteger(string sql)
240: {
241: // return a DataTable of result rows
242: int n;
243: SqlCommand sqlCommand;
244:
245: using (SqlConnection sqlConnection = new SqlConnection(connectionString))
246: {
247: sqlCommand = new SqlCommand(sql, sqlConnection);
248:
249: sqlConnection.Open();
250:
251: try
252: {
253: n = (System.Int16)sqlCommand.ExecuteScalar();
254: }
255: catch (Exception)
256: {
257: n = 0;
258: }
259:
260: //sc.Close();
261: }
262:
263: return n;
264: }
265:
266: ////////////////////////////////////////////////////////////////////////////
267:
268: /// <summary>
269: ///
270: /// </summary>
271: public string SmallDateTime(DateTime dateTime)
272: {
273: // return an SQL Server friendly string of a smalldatetime value
274: string s;
275:
276: //s = "'" + dt.ToString("yyyy-MM-ddTHH:mm:ss") + "'";
277: s = dateTime.ToString("yyyy-MM-ddTHH:mm:ss");
278:
279: return s;
280: }
281:
282: ////////////////////////////////////////////////////////////////////////////
283:
284: /// <summary>
285: ///
286: /// </summary>
287: public bool ImportXml(string tableName, string file)
288: {
289: bool b;
290: int i;
291: string c, path;
292: DataSet ds;
293: SqlDataAdapter sda;
294: SqlCommandBuilder scb;
295: DataRow dr;
296:
297: ds = new DataSet("ia_ngn");
298:
299: path = Ia.Cl.Model.Default.AbsolutePath();
300:
301: file = path + file;
302:
303: ds.ReadXml(file);
304:
305: using (SqlConnection sc = new SqlConnection(connectionString))
306: {
307: sc.Open();
308:
309: c = @"SELECT * FROM [" + tableName + @"]";
310: sda = new SqlDataAdapter(c, sc);
311: scb = new SqlCommandBuilder(sda);
312:
313: try
314: {
315: sda.Fill(ds, tableName);
316:
317: foreach (DataRow r in ds.Tables[tableName].Rows)
318: {
319: dr = ds.Tables[tableName].NewRow();
320:
321: for (i = 0; i < r.ItemArray.Length; i++) dr[i] = r[i];
322:
323: ds.Tables[0].Rows.Add(dr);
324: }
325:
326: scb.GetUpdateCommand();
327: sda.Update(ds, tableName);
328:
329: b = true;
330: }
331: catch (Exception) { b = false; }
332: finally
333: {
334: //sc.Close();
335: }
336: }
337:
338: return b;
339: }
340:
341: ////////////////////////////////////////////////////////////////////////////
342:
343: /// <summary>
344: ///
345: /// </summary>
346: public bool ExportXml(string tableName, string file)
347: {
348: // perform dump or backup of database table data into an XML document
349: bool b;
350: string c, path;
351: DataSet ds;
352: SqlDataAdapter sda;
353:
354: c = @"SELECT * FROM [" + tableName + @"]";
355:
356: using (SqlConnection sc = new SqlConnection(connectionString))
357: {
358: sc.Open();
359:
360: ds = new DataSet("ia_ngn");
361: sda = new SqlDataAdapter(c, sc);
362:
363: try
364: {
365: sda.Fill(ds, tableName);
366:
367: path = Ia.Cl.Model.Default.AbsolutePath();
368:
369: file = path + file;
370: ds.WriteXml(file, XmlWriteMode.WriteSchema);
371:
372: b = true;
373: }
374: catch (Exception) { b = false; }
375: finally
376: {
377: //sc.Close();
378: }
379: }
380:
381: return b;
382: }
383:
384: ////////////////////////////////////////////////////////////////////////////
385: ////////////////////////////////////////////////////////////////////////////
386: }
387: }