1 using System;
2
3 using System.Data;
4
5 using System.Text.RegularExpressions;
6
7 using System.Xml;
8
9 using System.IO;
10
11 using System.Collections;
12
13 using System.Data.SQLite;
14
15
16
17 namespace SQLite
18 {
19
20 /// <summary>
21
22 /// SQLiteHelper is a utility class similar to "SQLHelper" in MS
23
24 /// Data Access Application Block and follows similar pattern.
25
26 /// </summary>
27
28 public class SQLiteHelper
29 {
30
31 /// <summary>
32
33 /// Creates a new <see cref="SQLiteHelper"/> instance. The ctor is marked private since all members are static.
34
35 /// </summary>
36
37 private SQLiteHelper()
38 {
39
40 }
41
42 /// <summary>
43
44 /// Creates the command.
45
46 /// </summary>
47
48 /// <param name="connection">Connection.</param>
49
50 /// <param name="commandText">Command text.</param>
51
52 /// <param name="commandParameters">Command parameters.</param>
53
54 /// <returns>SQLite Command</returns>
55
56 public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
57 {
58
59 SQLiteCommand cmd = new SQLiteCommand(commandText, connection);
60
61 if (commandParameters.Length > 0)
62 {
63
64 foreach (SQLiteParameter parm in commandParameters)
65
66 cmd.Parameters.Add(parm);
67
68 }
69
70 return cmd;
71
72 }
73
74
75
76 /// <summary>
77
78 /// Creates the command.
79
80 /// </summary>
81
82 /// <param name="connectionString">Connection string.</param>
83
84 /// <param name="commandText">Command text.</param>
85
86 /// <param name="commandParameters">Command parameters.</param>
87
88 /// <returns>SQLite Command</returns>
89
90 public static SQLiteCommand CreateCommand(string connectionString, string commandText, params SQLiteParameter[] commandParameters)
91 {
92
93 SQLiteConnection cn = new SQLiteConnection(connectionString);
94
95
96
97 SQLiteCommand cmd = new SQLiteCommand(commandText, cn);
98
99
100
101 if (commandParameters.Length > 0)
102 {
103
104 foreach (SQLiteParameter parm in commandParameters)
105
106 cmd.Parameters.Add(parm);
107
108 }
109
110 return cmd;
111
112 }
113
114 /// <summary>
115
116 /// Creates the parameter.
117
118 /// </summary>
119
120 /// <param name="parameterName">Name of the parameter.</param>
121
122 /// <param name="parameterType">Parameter type.</param>
123
124 /// <param name="parameterValue">Parameter value.</param>
125
126 /// <returns>SQLiteParameter</returns>
127
128 public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue)
129 {
130
131 SQLiteParameter parameter = new SQLiteParameter();
132
133 parameter.DbType = parameterType;
134
135 parameter.ParameterName = parameterName;
136
137 parameter.Value = parameterValue;
138
139 return parameter;
140
141 }
142
143
144
145 /// <summary>
146
147 /// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
148
149 /// </summary>
150
151 /// <param name="connectionString">SQLite Connection string</param>
152
153 /// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param>
154
155 /// <param name="paramList">object[] array of parameter values</param>
156
157 /// <returns></returns>
158
159 public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList)
160 {
161
162 SQLiteConnection cn = new SQLiteConnection(connectionString);
163
164 SQLiteCommand cmd = cn.CreateCommand();
165
166
167
168
169
170 cmd.CommandText = commandText;
171
172 if (paramList != null)
173 {
174
175 AttachParameters(cmd, commandText, paramList);
176
177 }
178
179 DataSet ds = new DataSet();
180
181 if (cn.State == ConnectionState.Closed)
182
183 cn.Open();
184
185 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
186
187 da.Fill(ds);
188
189 da.Dispose();
190
191 cmd.Dispose();
192
193 cn.Close();
194
195 return ds;
196
197 }
198
199 /// <summary>
200
201 /// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
202
203 /// </summary>
204
205 /// <param name="cn">Connection.</param>
206
207 /// <param name="commandText">Command text.</param>
208
209 /// <param name="paramList">Param list.</param>
210
211 /// <returns></returns>
212
213 public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
214 {
215
216
217
218 SQLiteCommand cmd = cn.CreateCommand();
219
220
221
222
223
224 cmd.CommandText = commandText;
225
226 if (paramList != null)
227 {
228
229 AttachParameters(cmd, commandText, paramList);
230
231 }
232
233 DataSet ds = new DataSet();
234
235 if (cn.State == ConnectionState.Closed)
236
237 cn.Open();
238
239 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
240
241 da.Fill(ds);
242
243 da.Dispose();
244
245 cmd.Dispose();
246
247 cn.Close();
248
249 return ds;
250
251 }
252
253 /// <summary>
254
255 /// Executes the dataset from a populated Command object.
256
257 /// </summary>
258
259 /// <param name="cmd">Fully populated SQLiteCommand</param>
260
261 /// <returns>DataSet</returns>
262
263 public static DataSet ExecuteDataset(SQLiteCommand cmd)
264 {
265
266 if (cmd.Connection.State == ConnectionState.Closed)
267
268 cmd.Connection.Open();
269
270 DataSet ds = new DataSet();
271
272 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
273
274 da.Fill(ds);
275
276 da.Dispose();
277
278 cmd.Connection.Close();
279
280 cmd.Dispose();
281
282 return ds;
283
284 }
285
286
287
288 /// <summary>
289
290 /// Executes the dataset in a SQLite Transaction
291
292 /// </summary>
293
294 /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. </param>
295
296 /// <param name="commandText">Command text.</param>
297
298 /// <param name="commandParameters">Sqlite Command parameters.</param>
299
300 /// <returns>DataSet</returns>
301
302 /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
303
304 public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, params SQLiteParameter[] commandParameters)
305 {
306
307
308
309 if (transaction == null) throw new ArgumentNullException("transaction");
310
311 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
312
313 IDbCommand cmd = transaction.Connection.CreateCommand();
314
315 cmd.CommandText = commandText;
316
317 foreach (SQLiteParameter parm in commandParameters)
318 {
319
320 cmd.Parameters.Add(parm);
321
322 }
323
324 if (transaction.Connection.State == ConnectionState.Closed)
325
326 transaction.Connection.Open();
327
328 DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
329
330 return ds;
331
332 }
333
334
335
336 /// <summary>
337
338 /// Executes the dataset with Transaction and object array of parameter values.
339
340 /// </summary>
341
342 /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. </param>
343
344 /// <param name="commandText">Command text.</param>
345
346 /// <param name="commandParameters">object[] array of parameter values.</param>
347
348 /// <returns>DataSet</returns>
349
350 /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
351
352 public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, object[] commandParameters)
353 {
354
355
356
357 if (transaction == null) throw new ArgumentNullException("transaction");
358
359 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
360
361 IDbCommand cmd = transaction.Connection.CreateCommand();
362
363 cmd.CommandText = commandText;
364
365 AttachParameters((SQLiteCommand)cmd, cmd.CommandText, commandParameters);
366
367 if (transaction.Connection.State == ConnectionState.Closed)
368
369 transaction.Connection.Open();
370
371
372
373 DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
374
375 return ds;
376
377 }
378
379
380
381 #region UpdateDataset
382
383 /// <summary>
384
385 /// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
386
387 /// </summary>
388
389 /// <remarks>
390
391 /// e.g.:
392
393 /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
394
395 /// </remarks>
396
397 /// <param name="insertCommand">A valid SQL statement to insert new records into the data source</param>
398
399 /// <param name="deleteCommand">A valid SQL statement to delete records from the data source</param>
400
401 /// <param name="updateCommand">A valid SQL statement used to update records in the data source</param>
402
403 /// <param name="dataSet">The DataSet used to update the data source</param>
404
405 /// <param name="tableName">The DataTable used to update the data source.</param>
406
407 public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName)
408 {
409
410 if (insertCommand == null) throw new ArgumentNullException("insertCommand");
411
412 if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
413
414 if (updateCommand == null) throw new ArgumentNullException("updateCommand");
415
416 if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
417
418
419
420 // Create a SQLiteDataAdapter, and dispose of it after we are done
421
422 using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter())
423 {
424
425 // Set the data adapter commands
426
427 dataAdapter.UpdateCommand = updateCommand;
428
429 dataAdapter.InsertCommand = insertCommand;
430
431 dataAdapter.DeleteCommand = deleteCommand;
432
433
434
435 // Update the dataset changes in the data source
436
437 dataAdapter.Update(dataSet, tableName);
438
439
440
441 // Commit all the changes made to the DataSet
442
443 dataSet.AcceptChanges();
444
445 }
446
447 }
448
449 #endregion
450
451
452
453
454
455
456
457
458
459 /// <summary>
460
461 /// ShortCut method to return IDataReader
462
463 /// NOTE: You should explicitly close the Command.connection you passed in as
464
465 /// well as call Dispose on the Command after reader is closed.
466
467 /// We do this because IDataReader has no underlying Connection Property.
468
469 /// </summary>
470
471 /// <param name="cmd">SQLiteCommand Object</param>
472
473 /// <param name="commandText">SQL Statement with optional embedded "@param" style parameters</param>
474
475 /// <param name="paramList">object[] array of parameter values</param>
476
477 /// <returns>IDataReader</returns>
478
479 public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList)
480 {
481
482 if (cmd.Connection == null)
483
484 throw new ArgumentException("Command must have live connection attached.", "cmd");
485
486 cmd.CommandText = commandText;
487
488 AttachParameters(cmd, commandText, paramList);
489
490 if (cmd.Connection.State == ConnectionState.Closed)
491
492 cmd.Connection.Open();
493
494 IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
495
496 return rdr;
497
498 }
499
500
501
502 /// <summary>
503
504 /// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values
505
506 /// </summary>
507
508 /// <param name="connectionString">SQLite Connection String</param>
509
510 /// <param name="commandText">Sql Statement with embedded "@param" style parameters</param>
511
512 /// <param name="paramList">object[] array of parameter values</param>
513
514 /// <returns></returns>
515
516 public static int ExecuteNonQuery(string connectionString, string commandText, params object[] paramList)
517 {
518
519 SQLiteConnection cn = new SQLiteConnection(connectionString);
520
521 SQLiteCommand cmd = cn.CreateCommand();
522
523 cmd.CommandText = commandText;
524
525 AttachParameters(cmd, commandText, paramList);
526
527 if (cn.State == ConnectionState.Closed)
528
529 cn.Open();
530
531 int result = cmd.ExecuteNonQuery();
532
533 cmd.Dispose();
534
535 cn.Close();
536
537
538
539 return result;
540
541 }
542
543
544
545
546
547
548
549 public static int ExecuteNonQuery(SQLiteConnection cn, string commandText, params object[] paramList)
550 {
551
552
553
554 SQLiteCommand cmd = cn.CreateCommand();
555
556 cmd.CommandText = commandText;
557
558 AttachParameters(cmd, commandText, paramList);
559
560 if (cn.State == ConnectionState.Closed)
561
562 cn.Open();
563
564 int result = cmd.ExecuteNonQuery();
565
566 cmd.Dispose();
567
568 cn.Close();
569
570
571
572 return result;
573
574 }
575
576
577
578 /// <summary>
579
580 /// Executes non-query sql Statment with Transaction
581
582 /// </summary>
583
584 /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, /// and Command, all of which must be created prior to making this method call. </param>
585
586 /// <param name="commandText">Command text.</param>
587
588 /// <param name="paramList">Param list.</param>
589
590 /// <returns>Integer</returns>
591
592 /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
593
594 public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params object[] paramList)
595 {
596
597 if (transaction == null) throw new ArgumentNullException("transaction");
598
599 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
600
601 IDbCommand cmd = transaction.Connection.CreateCommand();
602
603 cmd.CommandText = commandText;
604
605 AttachParameters((SQLiteCommand)cmd, cmd.CommandText, paramList);
606
607 if (transaction.Connection.State == ConnectionState.Closed)
608
609 transaction.Connection.Open();
610
611 int result = cmd.ExecuteNonQuery();
612
613 cmd.Dispose();
614
615 return result;
616
617 }
618
619
620
621
622
623 /// <summary>
624
625 /// Executes the non query.
626
627 /// </summary>
628
629 /// <param name="cmd">CMD.</param>
630
631 /// <returns></returns>
632
633 public static int ExecuteNonQuery(IDbCommand cmd)
634 {
635
636 if (cmd.Connection.State == ConnectionState.Closed)
637
638 cmd.Connection.Open();
639
640 int result = cmd.ExecuteNonQuery();
641
642 cmd.Connection.Close();
643
644 cmd.Dispose();
645
646 return result;
647
648 }
649
650
651
652 /// <summary>
653
654 /// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values
655
656 /// </summary>
657
658 /// <param name="connectionString">SQLite Connection String</param>
659
660 /// <param name="commandText">SQL statment with embedded "@param" style parameters</param>
661
662 /// <param name="paramList">object[] array of param values</param>
663
664 /// <returns></returns>
665
666 public static object ExecuteScalar(string connectionString, string commandText, params object[] paramList)
667 {
668
669 SQLiteConnection cn = new SQLiteConnection(connectionString);
670
671 SQLiteCommand cmd = cn.CreateCommand();
672
673 cmd.CommandText = commandText;
674
675 AttachParameters(cmd, commandText, paramList);
676
677 if (cn.State == ConnectionState.Closed)
678
679 cn.Open();
680
681 object result = cmd.ExecuteScalar();
682
683 cmd.Dispose();
684
685 cn.Close();
686
687
688
689 return result;
690
691 }
692
693
694
695 /// <summary>
696
697 /// Execute XmlReader with complete Command
698
699 /// </summary>
700
701 /// <param name="command">SQLite Command</param>
702
703 /// <returns>XmlReader</returns>
704
705 public static XmlReader ExecuteXmlReader(IDbCommand command)
706 { // open the connection if necessary, but make sure we
707
708 // know to close it when we�re done.
709
710 if (command.Connection.State != ConnectionState.Open)
711 {
712
713 command.Connection.Open();
714
715 }
716
717
718
719 // get a data adapter
720
721 SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)command);
722
723 DataSet ds = new DataSet();
724
725 // fill the data set, and return the schema information
726
727 da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
728
729 da.Fill(ds);
730
731 // convert our dataset to XML
732
733 StringReader stream = new StringReader(ds.GetXml());
734
735 command.Connection.Close();
736
737 // convert our stream of text to an XmlReader
738
739 return new XmlTextReader(stream);
740
741 }
742
743
744
745
746
747
748
749 /// <summary>
750
751 /// Parses parameter names from SQL Statement, assigns values from object array , /// and returns fully populated ParameterCollection.
752
753 /// </summary>
754
755 /// <param name="commandText">Sql Statement with "@param" style embedded parameters</param>
756
757 /// <param name="paramList">object[] array of parameter values</param>
758
759 /// <returns>SQLiteParameterCollection</returns>
760
761 /// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement.</remarks>
762
763 private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
764 {
765
766 if (paramList == null || paramList.Length == 0) return null;
767
768
769
770 SQLiteParameterCollection coll = cmd.Parameters;
771
772 string parmString = commandText.Substring(commandText.IndexOf("@"));
773
774 // pre-process the string so always at least 1 space after a comma.
775
776 parmString = parmString.Replace(",", " ,");
777
778 // get the named parameters into a match collection
779
780 string pattern = @"(@)\S*(.*?)\b";
781
782 Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
783
784 MatchCollection mc = ex.Matches(parmString);
785
786 string[] paramNames = new string[mc.Count];
787
788 int i = 0;
789
790 foreach (Match m in mc)
791 {
792
793 paramNames[i] = m.Value;
794
795 i++;
796
797 }
798
799
800
801 // now let's type the parameters
802
803 int j = 0;
804
805 Type t = null;
806
807 foreach (object o in paramList)
808 {
809
810 t = o.GetType();
811
812
813
814 SQLiteParameter parm = new SQLiteParameter();
815
816 switch (t.ToString())
817 {
818
819
820
821 case ("DBNull"):
822
823 case ("Char"):
824
825 case ("SByte"):
826
827 case ("UInt16"):
828
829 case ("UInt32"):
830
831 case ("UInt64"):
832
833 throw new SystemException("Invalid data type");
834
835
836
837
838
839 case ("System.String"):
840
841 parm.DbType = DbType.String;
842
843 parm.ParameterName = paramNames[j];
844
845 parm.Value = (string)paramList[j];
846
847 coll.Add(parm);
848
849 break;
850
851
852
853 case ("System.Byte[]"):
854
855 parm.DbType = DbType.Binary;
856
857 parm.ParameterName = paramNames[j];
858
859 parm.Value = (byte[])paramList[j];
860
861 coll.Add(parm);
862
863 break;
864
865
866
867 case ("System.Int32"):
868
869 parm.DbType = DbType.Int32;
870
871 parm.ParameterName = paramNames[j];
872
873 parm.Value = (int)paramList[j];
874
875 coll.Add(parm);
876
877 break;
878
879
880
881 case ("System.Boolean"):
882
883 parm.DbType = DbType.Boolean;
884
885 parm.ParameterName = paramNames[j];
886
887 parm.Value = (bool)paramList[j];
888
889 coll.Add(parm);
890
891 break;
892
893
894
895 case ("System.DateTime"):
896
897 parm.DbType = DbType.DateTime;
898
899 parm.ParameterName = paramNames[j];
900
901 parm.Value = Convert.ToDateTime(paramList[j]);
902
903 coll.Add(parm);
904
905 break;
906
907
908
909 case ("System.Double"):
910
911 parm.DbType = DbType.Double;
912
913 parm.ParameterName = paramNames[j];
914
915 parm.Value = Convert.ToDouble(paramList[j]);
916
917 coll.Add(parm);
918
919 break;
920
921
922
923 case ("System.Decimal"):
924
925 parm.DbType = DbType.Decimal;
926
927 parm.ParameterName = paramNames[j];
928
929 parm.Value = Convert.ToDecimal(paramList[j]);
930
931 break;
932
933
934
935 case ("System.Guid"):
936
937 parm.DbType = DbType.Guid;
938
939 parm.ParameterName = paramNames[j];
940
941 parm.Value = (System.Guid)(paramList[j]);
942
943 break;
944
945
946
947 case ("System.Object"):
948
949
950
951 parm.DbType = DbType.Object;
952
953 parm.ParameterName = paramNames[j];
954
955 parm.Value = paramList[j];
956
957 coll.Add(parm);
958
959 break;
960
961
962
963 default:
964
965 throw new SystemException("Value is of unknown data type");
966
967
968
969 } // end switch
970
971
972
973 j++;
974
975 }
976
977 return coll;
978
979 }
980
981
982
983 /// <summary>
984
985 /// Executes non query typed params from a DataRow
986
987 /// </summary>
988
989 /// <param name="command">Command.</param>
990
991 /// <param name="dataRow">Data row.</param>
992
993 /// <returns>Integer result code</returns>
994
995 public static int ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow)
996 {
997
998 int retVal = 0;
999
1000
1001
1002 // If the row has values, the store procedure parameters must be initialized
1003
1004 if (dataRow != null && dataRow.ItemArray.Length > 0)
1005 {
1006
1007 // Set the parameters values
1008
1009 AssignParameterValues(command.Parameters, dataRow);
1010
1011
1012
1013 retVal = ExecuteNonQuery(command);
1014
1015 }
1016
1017 else
1018 {
1019
1020 retVal = ExecuteNonQuery(command);
1021
1022 }
1023
1024
1025
1026 return retVal;
1027
1028 }
1029
1030
1031
1032 /// <summary>
1033
1034 /// This method assigns dataRow column values to an IDataParameterCollection
1035
1036 /// </summary>
1037
1038 /// <param name="commandParameters">The IDataParameterCollection to be assigned values</param>
1039
1040 /// <param name="dataRow">The dataRow used to hold the command's parameter values</param>
1041
1042 /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>
1043
1044 protected internal static void AssignParameterValues(IDataParameterCollection commandParameters, DataRow dataRow)
1045 {
1046
1047 if (commandParameters == null || dataRow == null)
1048 {
1049
1050 // Do nothing if we get no data
1051
1052 return;
1053
1054 }
1055
1056
1057
1058 DataColumnCollection columns = dataRow.Table.Columns;
1059
1060
1061
1062 int i = 0;
1063
1064 // Set the parameters values
1065
1066 foreach (IDataParameter commandParameter in commandParameters)
1067 {
1068
1069 // Check the parameter name
1070
1071 if (commandParameter.ParameterName == null ||
1072
1073 commandParameter.ParameterName.Length <= 1)
1074
1075 throw new InvalidOperationException(string.Format(
1076
1077 "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
1078
1079 i, commandParameter.ParameterName));
1080
1081
1082
1083 if (columns.Contains(commandParameter.ParameterName))
1084
1085 commandParameter.Value = dataRow[commandParameter.ParameterName];
1086
1087 else if (columns.Contains(commandParameter.ParameterName.Substring(1)))
1088
1089 commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
1090
1091
1092
1093 i++;
1094
1095 }
1096
1097 }
1098
1099
1100
1101 /// <summary>
1102
1103 /// This method assigns dataRow column values to an array of IDataParameters
1104
1105 /// </summary>
1106
1107 /// <param name="commandParameters">Array of IDataParameters to be assigned values</param>
1108
1109 /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
1110
1111 /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>
1112
1113 protected void AssignParameterValues(IDataParameter[] commandParameters, DataRow dataRow)
1114 {
1115
1116 if ((commandParameters == null) || (dataRow == null))
1117 {
1118
1119 // Do nothing if we get no data
1120
1121 return;
1122
1123 }
1124
1125
1126
1127 DataColumnCollection columns = dataRow.Table.Columns;
1128
1129
1130
1131 int i = 0;
1132
1133 // Set the parameters values
1134
1135 foreach (IDataParameter commandParameter in commandParameters)
1136 {
1137
1138 // Check the parameter name
1139
1140 if (commandParameter.ParameterName == null ||
1141
1142 commandParameter.ParameterName.Length <= 1)
1143
1144 throw new InvalidOperationException(string.Format(
1145
1146 "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
1147
1148 i, commandParameter.ParameterName));
1149
1150
1151
1152 if (columns.Contains(commandParameter.ParameterName))
1153
1154 commandParameter.Value = dataRow[commandParameter.ParameterName];
1155
1156 else if (columns.Contains(commandParameter.ParameterName.Substring(1)))
1157
1158 commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
1159
1160
1161
1162 i++;
1163
1164 }
1165
1166 }
1167
1168
1169
1170 /// <summary>
1171
1172 /// This method assigns an array of values to an array of IDataParameters
1173
1174 /// </summary>
1175
1176 /// <param name="commandParameters">Array of IDataParameters to be assigned values</param>
1177
1178 /// <param name="parameterValues">Array of objects holding the values to be assigned</param>
1179
1180 /// <exception cref="System.ArgumentException">Thrown if an incorrect number of parameters are passed.</exception>
1181
1182 protected void AssignParameterValues(IDataParameter[] commandParameters, params object[] parameterValues)
1183 {
1184
1185 if ((commandParameters == null) || (parameterValues == null))
1186 {
1187
1188 // Do nothing if we get no data
1189
1190 return;
1191
1192 }
1193
1194
1195
1196 // We must have the same number of values as we pave parameters to put them in
1197
1198 if (commandParameters.Length != parameterValues.Length)
1199 {
1200
1201 throw new ArgumentException("Parameter count does not match Parameter Value count.");
1202
1203 }
1204
1205
1206
1207 // Iterate through the IDataParameters, assigning the values from the corresponding position in the
1208
1209 // value array
1210
1211 for (int i = 0, j = commandParameters.Length, k = 0; i < j; i++)
1212 {
1213
1214 if (commandParameters[i].Direction != ParameterDirection.ReturnValue)
1215 {
1216
1217 // If the current array value derives from IDataParameter, then assign its Value property
1218
1219 if (parameterValues[k] is IDataParameter)
1220 {
1221
1222 IDataParameter paramInstance;
1223
1224 paramInstance = (IDataParameter)parameterValues[k];
1225
1226 if (paramInstance.Direction == ParameterDirection.ReturnValue)
1227 {
1228
1229 paramInstance = (IDataParameter)parameterValues[++k];
1230
1231 }
1232
1233 if (paramInstance.Value == null)
1234 {
1235
1236 commandParameters[i].Value = DBNull.Value;
1237
1238 }
1239
1240 else
1241 {
1242
1243 commandParameters[i].Value = paramInstance.Value;
1244
1245 }
1246
1247 }
1248
1249 else if (parameterValues[k] == null)
1250 {
1251
1252 commandParameters[i].Value = DBNull.Value;
1253
1254 }
1255
1256 else
1257 {
1258
1259 commandParameters[i].Value = parameterValues[k];
1260
1261 }
1262
1263 k++;
1264
1265 }
1266
1267 }
1268
1269 }
1270
1271 }
1272
1273 }
浙公网安备 33010602011771号