MsSqlDataConnection.cs 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using System.Data;
  5. using System.Data.Common;
  6. using System.Data.SqlClient;
  7. namespace FastReport.Data
  8. {
  9. /// <summary>
  10. /// Represents a connection to MS SQL database.
  11. /// </summary>
  12. /// <example>This example shows how to add a new connection to the report.
  13. /// <code>
  14. /// Report report1;
  15. /// MsSqlDataConnection conn = new MsSqlDataConnection();
  16. /// conn.ConnectionString = "your_connection_string";
  17. /// report1.Dictionary.Connections.Add(conn);
  18. /// conn.CreateAllTables();
  19. /// </code>
  20. /// </example>
  21. public partial class MsSqlDataConnection : DataConnectionBase
  22. {
  23. /// <inheritdoc/>
  24. protected override string GetConnectionStringWithLoginInfo(string userName, string password)
  25. {
  26. SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
  27. builder.IntegratedSecurity = false;
  28. builder.UserID = userName;
  29. builder.Password = password;
  30. return builder.ToString();
  31. }
  32. /// <inheritdoc/>
  33. public override string QuoteIdentifier(string value, DbConnection connection)
  34. {
  35. // already quoted? keep in mind GetDBObjectNames and non-dbo schema!
  36. if (!value.EndsWith("\""))
  37. value = "\"" + value + "\"";
  38. return value;
  39. }
  40. /// <inheritdoc/>
  41. public override Type GetConnectionType()
  42. {
  43. return typeof(SqlConnection);
  44. }
  45. /// <inheritdoc/>
  46. public override DbDataAdapter GetAdapter(string selectCommand, DbConnection connection,
  47. CommandParameterCollection parameters)
  48. {
  49. SqlDataAdapter adapter = new SqlDataAdapter(selectCommand, connection as SqlConnection);
  50. foreach (CommandParameter p in parameters)
  51. {
  52. SqlParameter parameter = adapter.SelectCommand.Parameters.Add(p.Name, (SqlDbType)p.DataType, p.Size);
  53. object value = p.Value;
  54. parameter.Direction = p.Direction;
  55. if ((SqlDbType)p.DataType == SqlDbType.UniqueIdentifier && (value is Variant || value is String))
  56. value = new Guid(value.ToString());
  57. if (value is Variant && ((Variant)value).Type == typeof(string))
  58. {
  59. if (VariantToClrType((Variant)value, (SqlDbType)p.DataType) != null)
  60. parameter.Value = VariantToClrType((Variant)value, (SqlDbType)p.DataType);
  61. }
  62. else
  63. parameter.Value = value;
  64. }
  65. return adapter;
  66. }
  67. /// <inheritdoc/>
  68. public override Type GetParameterType()
  69. {
  70. return typeof(SqlDbType);
  71. }
  72. private void GetDBObjectNames(string name, List<string> list)
  73. {
  74. DataTable schema = null;
  75. DbConnection conn = GetConnection();
  76. try
  77. {
  78. OpenConnection(conn);
  79. schema = conn.GetSchema("Tables", new string[] { null, null, null, name });
  80. }
  81. finally
  82. {
  83. DisposeConnection(conn);
  84. }
  85. foreach (DataRow row in schema.Rows)
  86. {
  87. string tableName = row["TABLE_NAME"].ToString();
  88. string schemaName = row["TABLE_SCHEMA"].ToString();
  89. if (String.Compare(schemaName, "dbo") == 0)
  90. list.Add(tableName);
  91. else
  92. list.Add(schemaName + ".\"" + tableName + "\"");
  93. }
  94. }
  95. /// <inheritdoc/>
  96. public override string[] GetTableNames()
  97. {
  98. List<string> list = new List<string>();
  99. GetDBObjectNames("BASE TABLE", list);
  100. GetDBObjectNames("VIEW", list);
  101. return list.ToArray();
  102. }
  103. private object VariantToClrType(Variant value, SqlDbType type)
  104. {
  105. if (value.ToString() == "")
  106. return null;
  107. switch (type)
  108. {
  109. case SqlDbType.BigInt:
  110. {
  111. long val = 0;
  112. long.TryParse(value.ToString(), out val);
  113. return val;
  114. }
  115. case SqlDbType.Bit:
  116. {
  117. bool val = false;
  118. bool.TryParse(value.ToString(), out val);
  119. return val;
  120. }
  121. case SqlDbType.Char:
  122. case SqlDbType.NChar:
  123. case SqlDbType.NText:
  124. case SqlDbType.NVarChar:
  125. case SqlDbType.Text:
  126. case SqlDbType.VarChar:
  127. case SqlDbType.Xml:
  128. {
  129. if (value.ToString() == "")
  130. return null;
  131. return value.ToString();
  132. }
  133. case SqlDbType.DateTime:
  134. case SqlDbType.SmallDateTime:
  135. case SqlDbType.Date:
  136. case SqlDbType.Time:
  137. case SqlDbType.DateTime2:
  138. {
  139. DateTime val = DateTime.Now;
  140. DateTime.TryParse(value.ToString(), out val);
  141. return val;
  142. }
  143. case SqlDbType.Decimal:
  144. case SqlDbType.Money:
  145. case SqlDbType.SmallMoney:
  146. {
  147. decimal val = 0;
  148. decimal.TryParse(value.ToString(), out val);
  149. return val;
  150. }
  151. case SqlDbType.Real:
  152. case SqlDbType.Float:
  153. {
  154. float val = 0;
  155. float.TryParse(value.ToString(), out val);
  156. return val;
  157. }
  158. case SqlDbType.Int:
  159. {
  160. int val = 0;
  161. int.TryParse(value.ToString(), out val);
  162. return val;
  163. }
  164. case SqlDbType.UniqueIdentifier:
  165. {
  166. Guid val = Guid.Empty;
  167. Guid.TryParse(value.ToString(), out val);
  168. return val;
  169. }
  170. case SqlDbType.SmallInt:
  171. {
  172. short val = 0;
  173. short.TryParse(value.ToString(), out val);
  174. return val;
  175. }
  176. case SqlDbType.TinyInt:
  177. {
  178. byte val = 0;
  179. byte.TryParse(value.ToString(), out val);
  180. return val;
  181. }
  182. case SqlDbType.Variant:
  183. case SqlDbType.Udt:
  184. return value;
  185. case SqlDbType.Structured:
  186. throw new NotImplementedException();
  187. case SqlDbType.Binary:
  188. case SqlDbType.Image:
  189. case SqlDbType.Timestamp:
  190. case SqlDbType.VarBinary:
  191. throw new NotImplementedException();
  192. case SqlDbType.DateTimeOffset:
  193. {
  194. DateTimeOffset val = DateTimeOffset.Now;
  195. DateTimeOffset.TryParse(value.ToString(), out val);
  196. return val;
  197. }
  198. default:
  199. return value;
  200. }
  201. }
  202. ///<inheritdoc/>
  203. public override string[] GetProcedureNames()
  204. {
  205. List<string> list = new List<string>();
  206. DataTable schema = null;
  207. DbConnection conn = GetConnection();
  208. try
  209. {
  210. OpenConnection(conn);
  211. schema = conn.GetSchema("Procedures");
  212. }
  213. finally
  214. {
  215. DisposeConnection(conn);
  216. }
  217. foreach (DataRow row in schema.Rows)
  218. {
  219. string tableName = row["SPECIFIC_NAME"].ToString();
  220. string schemaName = row["SPECIFIC_SCHEMA"].ToString();
  221. if (tableName.StartsWith("dt_"))
  222. continue;
  223. if (String.Compare(schemaName, "dbo") == 0)
  224. list.Add(tableName);
  225. else
  226. list.Add(schemaName + ".\"" + tableName + "\"");
  227. }
  228. return list.ToArray();
  229. }
  230. ///<inheritdoc/>
  231. public override TableDataSource CreateProcedure(string tableName)
  232. {
  233. ProcedureDataSource table = new ProcedureDataSource();
  234. table.Enabled = true;
  235. table.SelectCommand = tableName;
  236. DbConnection conn = GetConnection();
  237. try
  238. {
  239. OpenConnection(conn);
  240. var schemaParameters = conn.GetSchema("ProcedureParameters", new string[] { null, null, tableName });
  241. foreach (DataRow row in schemaParameters.Rows)
  242. {
  243. ParameterDirection direction = ParameterDirection.Input;
  244. switch (row["PARAMETER_MODE"].ToString())
  245. {
  246. case "IN":
  247. direction = ParameterDirection.Input;
  248. table.Enabled = false;
  249. break;
  250. case "INOUT":
  251. direction = ParameterDirection.InputOutput;
  252. table.Enabled = false;
  253. break;
  254. case "OUT":
  255. direction = ParameterDirection.Output;
  256. break;
  257. }
  258. table.Parameters.Add(new ProcedureParameter()
  259. {
  260. Name = row["PARAMETER_NAME"].ToString(),
  261. DataType = (int)(SqlDbType)Enum.Parse(typeof(SqlDbType), row["DATA_TYPE"].ToString(), true),
  262. Direction = direction
  263. });
  264. }
  265. }
  266. finally
  267. {
  268. DisposeConnection(conn);
  269. }
  270. return table;
  271. }
  272. /// <inheritdoc/>
  273. public MsSqlDataConnection() : base()
  274. {
  275. CanContainProcedures = true;
  276. }
  277. }
  278. }