PageRenderTime 192ms CodeModel.GetById 20ms RepoModel.GetById 9ms app.codeStats 0ms

/src/Blizzard/DAL/SqlClientPlugin.cs

#
C# | 267 lines | 175 code | 76 blank | 16 comment | 8 complexity | 62f2ba21db1dd6e40ece58207a66bd57 MD5 | raw file
Possible License(s): Apache-2.0
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Data.SqlClient;
  6. using System.Data;
  7. namespace Blizzard.DAL
  8. {
  9. public class SqlClientPlugin : IDAO
  10. {
  11. SqlConnection _cnn = new SqlConnection();
  12. public SqlClientPlugin(string connectionString)
  13. {
  14. _cnn.ConnectionString = connectionString;
  15. }
  16. public DataTable GetDataTable(string table,string whereClause)
  17. {
  18. SqlCommand cmd = new SqlCommand( "Select * from " + table + " " + whereClause);
  19. return getDataTable(cmd);
  20. }
  21. public DataTable GetDataTable(string table, string identityFieldName ,int id)
  22. {
  23. SqlCommand cmd = new SqlCommand("Select * From " + table + " where " + identityFieldName + " = " + id);
  24. return getDataTable(cmd);
  25. }
  26. public DataTable GetManyToManyAssociates(string associateTable, string joinTable, string associatedId, string joinTableAssociateId, string joinTableMainId, int mainObjectId)
  27. {
  28. string sql = "Select A.* From " + associateTable + " A inner join " + joinTable +
  29. " J on A." + associatedId + " = J." + joinTableAssociateId +
  30. " where " + joinTableMainId + " = " + mainObjectId;
  31. SqlCommand cmd = new SqlCommand(sql);
  32. return getDataTable(cmd);
  33. }
  34. public DataTable GetParent(string associateTable, string mainTable, string associateTableIdField, string associateIdFieldInMainTable, string mainTableIdField, int mainObjectId)
  35. {
  36. string sql = "select A.* from " + associateTable + " A inner join " +
  37. mainTable + " M on A." + associateTableIdField + " = M." + associateIdFieldInMainTable +
  38. " where M." + mainTableIdField + " = " + mainObjectId;
  39. SqlCommand cmd = new SqlCommand(sql);
  40. return getDataTable(cmd);
  41. }
  42. public DataTable GetChilderen(string associateTable, string mainTable, string mainTableIdFieldInAssociateTable, string mainTableIdField, int mainObjectId)
  43. {
  44. string sql = "select A.* from " + associateTable + " A inner join " +
  45. mainTable + " M on A." + mainTableIdFieldInAssociateTable + " = M." + mainTableIdField +
  46. " where M." + mainTableIdField + " = " + mainObjectId;
  47. SqlCommand cmd = new SqlCommand(sql);
  48. return getDataTable(cmd);
  49. }
  50. public void JoinTableDeleter(string joinTable, string fieldNameOne, string fieldNameTwo, int filedOneValue, int fieldTwoValue)
  51. {
  52. string sql = " Delete from " + joinTable + " where " +
  53. fieldNameOne + " = " + filedOneValue + " and " +
  54. fieldNameTwo + " = " + fieldTwoValue;
  55. SqlCommand cmd = new SqlCommand(sql);
  56. executeNonQuery(cmd);
  57. }
  58. public void DeleteRecord(string tableName, string identityFieldName, int identityFieldValue)
  59. {
  60. string sql = " delete from " + tableName + " where " + identityFieldName + " = " + identityFieldValue;
  61. SqlCommand cmd = new SqlCommand(sql);
  62. executeNonQuery(cmd);
  63. }
  64. public void Insert(string tableName, Dictionary<string, object> fieldValuePairs)
  65. {
  66. SqlCommand cmd = new SqlCommand();
  67. string sql = "INSERT INTO " + tableName + " ( ";
  68. foreach (KeyValuePair<string,object> fieldValue in fieldValuePairs)
  69. {
  70. string field = fieldValue.Key;
  71. //A field can eventualy contain '[' or ']' characters
  72. //(for example if the field name is a reserved word or
  73. //the filed name contains spaces), thats why for the name
  74. //of a parameter we should delete them
  75. string parameterName = field.Replace("[", "");
  76. parameterName = parameterName.Replace("]", "");
  77. parameterName = parameterName.Replace(" ", "");
  78. parameterName = "@" + parameterName;
  79. object propertyValue = fieldValue.Value;
  80. if (propertyValue == null)
  81. {
  82. cmd.Parameters.Add(new SqlParameter(parameterName, DBNull.Value));
  83. }
  84. else
  85. {
  86. cmd.Parameters.Add(new SqlParameter(parameterName, propertyValue));
  87. }
  88. sql = sql + field + ",";
  89. }
  90. sql = sql.TrimEnd(',');
  91. sql = sql + ") VALUES (";
  92. foreach (SqlParameter parameter in cmd.Parameters)
  93. {
  94. sql = sql + parameter + ",";
  95. }
  96. sql = sql.TrimEnd(',');
  97. sql = sql + ")";
  98. cmd.CommandText = sql;
  99. executeNonQuery(cmd);
  100. }
  101. public int GetTheLastInsertedId(string tableName , string identityFieldName)
  102. {
  103. SqlCommand cmd = new SqlCommand("Select MAX(" + identityFieldName + ") From " + tableName);
  104. return (int)executeScalar(cmd);
  105. }
  106. public void Update(string tableName, string identityFieldName, int identityFieldValue, Dictionary<string, object> fieldValuePairs)
  107. {
  108. SqlCommand cmd = new SqlCommand();
  109. string sql = "Update " + tableName + " set ";
  110. foreach (KeyValuePair<string, object> fieldValue in fieldValuePairs)
  111. {
  112. string field = fieldValue.Key;
  113. //A field can eventualy contain '[' or ']' characters
  114. //(for example if the field name is a reserved word or
  115. //the filed name contains spaces), thats why for the name
  116. //of a parameter we should delete them
  117. string parameterName = field.Replace("[", "");
  118. parameterName = parameterName.Replace("]", "");
  119. parameterName = parameterName.Replace(" ", "");
  120. parameterName = "@" + parameterName;
  121. object propertyValue = fieldValue.Value;
  122. SqlParameter parameter = new SqlParameter(parameterName, propertyValue);
  123. cmd.Parameters.Add(parameter);
  124. sql = sql + field + " = " + parameter + ",";
  125. }
  126. sql = sql.TrimEnd(',');
  127. sql = sql + " where " + identityFieldName + " = " + identityFieldValue;
  128. cmd.CommandText = sql;
  129. executeNonQuery(cmd);
  130. }
  131. public void JoinTableInserter(string joinTable, string fieldNameOne, string fieldNameTwo, int filedValueOne, int fieldValueTwo)
  132. {
  133. SqlCommand cmd = new SqlCommand();
  134. //A field can eventualy contain '[' or ']' characters
  135. //(for example if the field name is a reserved word or
  136. //the filed name contains spaces), thats why for the name
  137. //of a parameter we should delete them
  138. string parameterName1 = fieldNameOne.Replace("[", "");
  139. parameterName1 = parameterName1.Replace("]", "");
  140. parameterName1 = parameterName1.Replace(" ", "");
  141. parameterName1 = "@" + parameterName1;
  142. string parameterName2 = fieldNameTwo.Replace("[", "");
  143. parameterName2 = parameterName2.Replace("]", "");
  144. parameterName2 = parameterName2.Replace(" ", "");
  145. parameterName2 = "@" + parameterName2;
  146. SqlParameter parameter1 = new SqlParameter(parameterName1, filedValueOne);
  147. SqlParameter parameter2 = new SqlParameter(parameterName2, fieldValueTwo);
  148. cmd.Parameters.Add(parameter1);
  149. cmd.Parameters.Add(parameter2);
  150. string sql = "Insert into " + joinTable + " ( " + fieldNameOne +
  151. " , " + fieldNameTwo + " ) Values ( " + parameter1 + " , " + parameter2 + " ) ";
  152. cmd.CommandText = sql;
  153. executeNonQuery(cmd);
  154. }
  155. public void SetParentId(string childTable, string childIdField, int childId, string parentIdFieldInChildTable, int parentIdValue)
  156. {
  157. string sql = " Update " + childTable + " set " + parentIdFieldInChildTable + " = " + parentIdValue + " where " +
  158. childIdField + " = " + childId;
  159. SqlCommand cmd = new SqlCommand(sql);
  160. executeNonQuery(cmd);
  161. }
  162. //===============================================private methods==================================================
  163. //===============================================private methods==================================================
  164. //===============================================private methods==================================================
  165. //===============================================private methods==================================================
  166. private void executeNonQuery(SqlCommand cmd)
  167. {
  168. cmd.Connection = _cnn;
  169. if (_cnn.State != ConnectionState.Open)
  170. _cnn.Open();
  171. cmd.ExecuteNonQuery();
  172. }
  173. private object executeScalar(SqlCommand cmd)
  174. {
  175. cmd.Connection = _cnn;
  176. if (_cnn.State != ConnectionState.Open)
  177. _cnn.Open();
  178. return cmd.ExecuteScalar();
  179. }
  180. private DataTable getDataTable(SqlCommand cmd)
  181. {
  182. DataTable dt = new DataTable();
  183. if (_cnn.State != ConnectionState.Open)
  184. _cnn.Open();
  185. cmd.Connection = _cnn;
  186. SqlDataAdapter da = new SqlDataAdapter(cmd);
  187. da.Fill(dt);
  188. return dt;
  189. }
  190. }
  191. }