C # using DataTable to access data,
DataTable Introduction: http://www.dotnetperls.com/datatable
Retrieve Data
Example of saving SQL results to DataTable: http://stackoverflow.com/questions/6073382/read-sql-table-into-c-sharp-datatable
Example code:
String conStr = @ "server = stcvm-130; integrated security = true; database = RAADB"; SqlConnection con = new SqlConnection (conStr); SqlParameter catName = new SqlParameter ("@ CatName ", "shampoo"); SqlCommand com = new SqlCommand (); com. commandType = System. data. commandType. storedProcedure; com. connection = con; com. commandText = "dbo. prc_GetCatPriceRange "; com. commandTimeout = 90; com. parameters. add (catName); com. parameters. add ("RETURN_VALUE", SqlDbType. int ). direction = ParameterDirection. returnValue; DataTable able = new dataTable (); // The following two can also be replaced by 'sqldataadapter da = new SqlDataAdapter (com); 'sqldataadapter da = new SqlDataAdapter (); da. selectCommand = com; // This will query your database and return the result to your datatable da. fill (dataTable); da. dispose (); Console. writeLine (dataTable. rows. count); foreach (DataRow dr in dataTable. rows) Console. writeLine (dr ["minPrice"]. toString () + "" + dr [1]. toString ());
The key point here is the SqlDataAdapter class. There are many constructor functions. You can just specify the key parameters. For example:
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM table",constring);
When using the relevant code, one problem is that after execution, a stored procedure returns normal results, and the other returns 0 rows, the fact is that it should also have a row of returned results. After trying for a long time, the result is that, although the using part has no compilation error, it is better to add some solution packages. This is really strange!
Store Data
Currently, you can use SqlBulkCopy and Transaction to implement this function. The following is the available reference code:
public class Person { public int PersonId { get; set; } public string LastName { get; set; } public string FirstName { get; set; } public string Adress { get; set; } public string City { get; set; } public Person(int id, string ln, string fn, string ad, string ci) { PersonId = id; LastName = ln; FirstName = fn; Adress = ad; City = ci; } }
List<Tuple<string, Type, string>> COL_DEFs = new List<Tuple<string, Type, string>>() { new Tuple<string, Type, string>("PersonId", typeof(int), "Id_P"), new Tuple<string, Type, string>("LastName", typeof(string), "LastName"), new Tuple<string, Type, string>("FirstName", typeof(string), "FirstName"), new Tuple<string, Type, string>("Adress", typeof(string), "Adress"), new Tuple<string, Type, string>("City", typeof(string), "City") }; string tableName = "dbo.Persons"; DataTable dt = new DataTable(tableName); foreach (var col in COL_DEFs) { // The column format: <ObjectAttributeName, Type, DBColumnName> dt.Columns.Add(col.Item3, col.Item2); } List<Person> records = new List<Person>(); records.Add(new Person(30,"Jim","Gree","Nanjing","JS")); records.Add(new Person(40, "Jimf", "Gree", "Nanjing", "GF")); foreach (var rec in records) { DataRow dr = dt.NewRow(); foreach (var col in COL_DEFs) { dr[col.Item3] = rec.GetType().GetProperty(col.Item1).GetValue(rec, null); } dt.Rows.Add(dr); } var transaction = con.BeginTransaction(); SqlBulkCopy bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, transaction); bulkCopy.BatchSize = 10000; bulkCopy.BulkCopyTimeout = 36000; bulkCopy.DestinationTableName = dt.TableName; bulkCopy.WriteToServer(dt); transaction.Commit();