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 }