C # using DataTable to access data,

Source: Internet
Author: User

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();



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.