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: bulkCopy.DestinationTableName = dataTable.TableName;
130:
131: try
132: {
133: bulkCopy.WriteToServer(dataTable);
134:
135: result.AddSuccess("DataTable copied: " + dataTable.TableName + " had " + dataTable.Rows.Count + " record(s). ");
136: }
137: catch (Exception ex)
138: {
139: result.AddError(ex.Message);
140: }
141: }
142: else
143: {
144: result.AddWarning("DataTable " + dataTable.TableName + " is null or row count is zero. ");
145: }
146: }
147: }
148: }
149:
150: ////////////////////////////////////////////////////////////////////////////
151:
152: /// <summary>
153: /// Return a DataTable of properties of tables that start with "ia_"
154: /// </summary>
155: public DataTable Schema()
156: {
157: 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_%')");
158:
159: var dataColumns = new DataColumn[1];
160:
161: dataColumns[0] = dataTable.Columns["id"];
162:
163: dataTable.PrimaryKey = dataColumns;
164:
165: return dataTable;
166: }
167:
168: ////////////////////////////////////////////////////////////////////////////
169:
170: /// <summary>
171: ///
172: /// </summary>
173: public string Scalar(string sql)
174: {
175: string s;
176: SqlCommand sqlCommand;
177:
178: using (SqlConnection sqlConnection = new SqlConnection(connectionString))
179: {
180: sqlCommand = new SqlCommand(sql, sqlConnection);
181:
182: sqlConnection.Open();
183:
184: try
185: {
186: s = sqlCommand.ExecuteScalar().ToString();
187: }
188: catch
189: {
190: s = null;
191: }
192:
193: //sc.Close();
194: }
195:
196: return s;
197: }
198:
199: ////////////////////////////////////////////////////////////////////////////
200:
201: /// <summary>
202: ///
203: /// </summary>
204: public int ScalarInteger(string sql)
205: {
206: // return a DataTable of result rows
207: int n;
208: SqlCommand sqlCommand;
209:
210: using (SqlConnection sqlConnection = new SqlConnection(connectionString))
211: {
212: sqlCommand = new SqlCommand(sql, sqlConnection);
213:
214: sqlConnection.Open();
215:
216: try
217: {
218: n = (System.Int32)sqlCommand.ExecuteScalar();
219: }
220: catch (Exception)
221: {
222: n = 0;
223: }
224:
225: //sc.Close();
226: }
227:
228: return n;
229: }
230:
231: ////////////////////////////////////////////////////////////////////////////
232:
233: /// <summary>
234: ///
235: /// </summary>
236: public int ScalarSmallInteger(string sql)
237: {
238: // return a DataTable of result rows
239: int n;
240: SqlCommand sqlCommand;
241:
242: using (SqlConnection sqlConnection = new SqlConnection(connectionString))
243: {
244: sqlCommand = new SqlCommand(sql, sqlConnection);
245:
246: sqlConnection.Open();
247:
248: try
249: {
250: n = (System.Int16)sqlCommand.ExecuteScalar();
251: }
252: catch (Exception)
253: {
254: n = 0;
255: }
256:
257: //sc.Close();
258: }
259:
260: return n;
261: }
262:
263: ////////////////////////////////////////////////////////////////////////////
264:
265: /// <summary>
266: ///
267: /// </summary>
268: public string SmallDateTime(DateTime dateTime)
269: {
270: // return an SQL Server friendly string of a smalldatetime value
271: string s;
272:
273: //s = "'" + dt.ToString("yyyy-MM-ddTHH:mm:ss") + "'";
274: s = dateTime.ToString("yyyy-MM-ddTHH:mm:ss");
275:
276: return s;
277: }
278:
279: ////////////////////////////////////////////////////////////////////////////
280:
281: /// <summary>
282: ///
283: /// </summary>
284: public bool ImportXml(string tableName, string file)
285: {
286: bool b;
287: int i;
288: string c, path;
289: DataSet ds;
290: SqlDataAdapter sda;
291: SqlCommandBuilder scb;
292: DataRow dr;
293:
294: ds = new DataSet("ia_ngn");
295:
296: path = Ia.Cl.Model.Default.AbsolutePath();
297:
298: file = path + file;
299:
300: ds.ReadXml(file);
301:
302: using (SqlConnection sc = new SqlConnection(connectionString))
303: {
304: sc.Open();
305:
306: c = @"SELECT * FROM [" + tableName + @"]";
307: sda = new SqlDataAdapter(c, sc);
308: scb = new SqlCommandBuilder(sda);
309:
310: try
311: {
312: sda.Fill(ds, tableName);
313:
314: foreach (DataRow r in ds.Tables[tableName].Rows)
315: {
316: dr = ds.Tables[tableName].NewRow();
317:
318: for (i = 0; i < r.ItemArray.Length; i++) dr[i] = r[i];
319:
320: ds.Tables[0].Rows.Add(dr);
321: }
322:
323: scb.GetUpdateCommand();
324: sda.Update(ds, tableName);
325:
326: b = true;
327: }
328: catch (Exception) { b = false; }
329: finally
330: {
331: //sc.Close();
332: }
333: }
334:
335: return b;
336: }
337:
338: ////////////////////////////////////////////////////////////////////////////
339:
340: /// <summary>
341: ///
342: /// </summary>
343: public bool ExportXml(string tableName, string file)
344: {
345: // perform dump or backup of database table data into an XML document
346: bool b;
347: string c, path;
348: DataSet ds;
349: SqlDataAdapter sda;
350:
351: c = @"SELECT * FROM [" + tableName + @"]";
352:
353: using (SqlConnection sc = new SqlConnection(connectionString))
354: {
355: sc.Open();
356:
357: ds = new DataSet("ia_ngn");
358: sda = new SqlDataAdapter(c, sc);
359:
360: try
361: {
362: sda.Fill(ds, tableName);
363:
364: path = Ia.Cl.Model.Default.AbsolutePath();
365:
366: file = path + file;
367: ds.WriteXml(file, XmlWriteMode.WriteSchema);
368:
369: b = true;
370: }
371: catch (Exception) { b = false; }
372: finally
373: {
374: //sc.Close();
375: }
376: }
377:
378: return b;
379: }
380:
381: ////////////////////////////////////////////////////////////////////////////
382: ////////////////////////////////////////////////////////////////////////////
383: }
384: }