Csharp/CSharp Tutorial/ADO.Net/DataRow

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

Accessing Data Values in a DataRow Array by column name

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect ="SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable();
           da.Fill(dt);
           DataRow[] dra = new DataRow[dt.Rows.Count];
           dt.Rows.CopyTo(dra, 0);
           Console.WriteLine("FirstName = {0}", dra[2]["FirstName"]);
       }
   }</source>

Accessing Data Values in a DataRow Array by column name and return default value

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect ="SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable();
           da.Fill(dt);
           DataRow[] dra = new DataRow[dt.Rows.Count];
           dt.Rows.CopyTo(dra, 0);
           Console.WriteLine("FirstName = {0}",dra[2]["FirstName", DataRowVersion.Default]);
       }
   }</source>

Accessing Data Values in a DataRow Array by index

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect ="SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable();
           da.Fill(dt);
           DataRow[] dra = new DataRow[dt.Rows.Count];
           dt.Rows.CopyTo(dra, 0);
           Console.WriteLine("FirstName = {0}", dra[2][1]);
       }
   }</source>

Accessing Data Values in a DataRow Array in a generic way by index

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect ="SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable();
           da.Fill(dt);
           DataRow[] dra = new DataRow[dt.Rows.Count];
           dt.Rows.CopyTo(dra, 0);
           Console.WriteLine("FirstName = {0}", dra[2].Field<string>(1));
       }
   }</source>

Accessing Data Values in a DataRow Array in a generic way by name

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect ="SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable();
           da.Fill(dt);
           DataRow[] dra = new DataRow[dt.Rows.Count];
           dt.Rows.CopyTo(dra, 0);
           Console.WriteLine("FirstName = {0}",dra[2].Field<string>("FirstName"));
           Console.WriteLine("FirstName = {0}",dra[2].Field<string>("FirstName", DataRowVersion.Default));
       }
   }</source>

Create and fill the DataRow array

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           
           DataTable dt1 = new DataTable();
           da.Fill(dt1);
           DataRow[] dra = new DataRow[dt1.Rows.Count];
           dt1.Rows.CopyTo(dra, 0);
           for (int i = 0; i < dra.Length; i++){
               Console.WriteLine(
                   "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                   dra[i].Field<int>("ContactID"), dra[i].Field<string>("FirstName"),
                   dra[i].Field<string>("LastName"));
           }
       }
   }</source>

DataRowState manipulation

<source lang="csharp">using System; using System.Collections.Generic; using System.Text; using System.Data; using System.IO; using System.Runtime.Serialization.Formatters.Binary;

 class Program
 {
   static void Main(string[] args)
   {
     DataTable temp = new DataTable("Temp");
     temp.Columns.Add(new DataColumn("TempColumn", typeof(int)));
     DataRow row = temp.NewRow();           
     Console.WriteLine("After calling NewRow(): {0}", row.RowState);
     temp.Rows.Add(row);
     Console.WriteLine("After calling Rows.Add(): {0}", row.RowState);
     row["TempColumn"] = 10;
     Console.WriteLine("After first assignment: {0}", row.RowState);
     temp.AcceptChanges();
     Console.WriteLine("After calling AcceptChanges: {0}", row.RowState);
     row["TempColumn"] = 11;
     Console.WriteLine("After first assignment: {0}", row.RowState);
     temp.Rows[0].Delete();
     Console.WriteLine("After calling Delete: {0}", row.RowState);
   }
 }</source>

DataRow Update

<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.rumon; public class DataRowUpdateState : System.Windows.Forms.Form {

 private System.Windows.Forms.DataGrid dataGrid1;
 private System.ruponentModel.Container components = null;
 public DataRowUpdateState()
 {
   InitializeComponent();
   CreateCustomersTable();
 }
 protected override void Dispose( bool disposing )
 {
   if( disposing )
   {
     if (components != null) 
     {
       components.Dispose();
     }
   }
   base.Dispose( disposing );
 }
 private void InitializeComponent()
 {
   this.dataGrid1 = new System.Windows.Forms.DataGrid();
   ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
   this.SuspendLayout();
   // 
   // dataGrid1
   // 
   this.dataGrid1.DataMember = "";
   this.dataGrid1.Location = new System.Drawing.Point(8, 8);
   this.dataGrid1.Name = "dataGrid1";
   this.dataGrid1.Size = new System.Drawing.Size(416, 296);
   this.dataGrid1.TabIndex = 0;
   // 
   // DataRowUpdateState
   // 
   this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
   this.ClientSize = new System.Drawing.Size(432, 309);
   this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                   this.dataGrid1});
   this.Name = "DataRowUpdateState";
   this.Text = "DataRowUpdateState";
   ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
   this.ResumeLayout(false);
 }
 [STAThread]
 static void Main() 
 {
   Application.Run(new DataRowUpdateState());
 }
 private void CreateCustomersTable()
 {
   System.Data.DataTable custTable = new DataTable("Customers");
   DataColumn dtColumn;
   
   dtColumn = new DataColumn();
   dtColumn.DataType = System.Type.GetType("System.Int32");
   dtColumn.ColumnName = "id";
   dtColumn.Caption = "Cust ID";
   dtColumn.ReadOnly = true;
   dtColumn.Unique = true;
   custTable.Columns.Add(dtColumn);
   dtColumn = new DataColumn();
   dtColumn.DataType = System.Type.GetType("System.String");
   dtColumn.ColumnName = "Name";
   dtColumn.Caption = "Cust Name";
   dtColumn.AutoIncrement = false;
   dtColumn.ReadOnly = false;
   dtColumn.Unique = false;
   custTable.Columns.Add(dtColumn);
   dtColumn = new DataColumn();
   dtColumn.DataType = System.Type.GetType("System.String");
   dtColumn.ColumnName = "Address";
   dtColumn.Caption = "Address";
   dtColumn.ReadOnly = false;
   dtColumn.Unique = false;
   // Add Address column to the table.
   custTable.Columns.Add(dtColumn);
   DataColumn[] PrimaryKeyColumns = new DataColumn[1];
   PrimaryKeyColumns[0] = custTable.Columns["id"];
   custTable.PrimaryKey = PrimaryKeyColumns;
   DataSet ds = new DataSet("Customers");
   ds.Tables.Add(custTable);
   DataRow row1 = custTable.NewRow();
   row1["id"] = 1;
   row1["Address"] = "USA";
   row1["Name"] = "George";
   custTable.Rows.Add(row1);
       MessageBox.Show(row1.RowState.ToString());
   row1.RejectChanges();
   MessageBox.Show(row1.RowState.ToString());
   row1.Delete();
   MessageBox.Show(row1.RowState.ToString());
   
   dataGrid1.DataSource = ds.DefaultViewManager;      
 }

}</source>

DataView and DataRowView

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class DataViews
   {
       static void Main(string[] args)
       {
           string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
           string sql = @"select contactname,country from customers";
           SqlConnection conn = new SqlConnection(connString);
           try
           {
               SqlDataAdapter da = new SqlDataAdapter();
               da.SelectCommand = new SqlCommand(sql, conn);
               DataSet ds = new DataSet();
               da.Fill(ds, "customers");
               DataTable dt = ds.Tables["customers"];
               DataView dv = new DataView(dt,"country = "Germany"","country",DataViewRowState.CurrentRows);
               foreach (DataRowView drv in dv)
               {
                   for (int i = 0; i < dv.Table.Columns.Count; i++)
                       Console.Write(drv[i] + "\t");
               }
           }
           catch (Exception e)
           {
               Console.WriteLine("Error: " + e);
           }
           finally
           {
               conn.Close();
           } Console.ReadLine();
       }
   }</source>

Filling a DataTable from the DataRow array using CopyToDataTable()

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           
           DataTable dt1 = new DataTable();
           da.Fill(dt1);
           DataRow[] dra = new DataRow[dt1.Rows.Count];
           dt1.Rows.CopyTo(dra, 0);
           for (int i = 0; i < dra.Length; i++){
               Console.WriteLine(
                   "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                   dra[i].Field<int>("ContactID"), dra[i].Field<string>("FirstName"),
                   dra[i].Field<string>("LastName"));
           }
           DataTable dt2 = dra.CopyToDataTable();
           foreach (DataRow row in dt2.Rows)
           {
               Console.WriteLine(
                   "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                   row["ContactID"], row["FirstName"], row["LastName"]);
           }
       }
   }</source>

Filling a DataTable from the DataRow array using CopyToDataTable(DataTable, LoadOption)

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           
           DataTable dt1 = new DataTable();
           da.Fill(dt1);
           DataRow[] dra = new DataRow[dt1.Rows.Count];
           dt1.Rows.CopyTo(dra, 0);
           for (int i = 0; i < dra.Length; i++){
               Console.WriteLine(
                   "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                   dra[i].Field<int>("ContactID"), dra[i].Field<string>("FirstName"),
                   dra[i].Field<string>("LastName"));
           }
           DataTable dt2 = dra.CopyToDataTable();
           foreach (DataRow row in dt2.Rows)
           {
               Console.WriteLine(
                   "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                   row["ContactID"], row["FirstName"], row["LastName"]);
           }
           DataTable dt3 = dt1.Clone();
           dra.CopyToDataTable(dt3, LoadOption.Upsert);
           foreach (DataRow row in dt3.Rows)
           {
               Console.WriteLine("ContactID = {0}\tFirstName = {1}\tLastName = {2}",row["ContactID"], row["FirstName"], row["LastName"]);
           }            
       }
   }</source>

For loop over DataRow array

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           
           DataTable dt1 = new DataTable();
           da.Fill(dt1);
           DataRow[] dra = new DataRow[dt1.Rows.Count];
           dt1.Rows.CopyTo(dra, 0);
           for (int i = 0; i < dra.Length; i++){
               Console.WriteLine(
                   "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                   dra[i].Field<int>("ContactID"), dra[i].Field<string>("FirstName"),
                   dra[i].Field<string>("LastName"));
           }
       }
   }</source>

Get column value in a row

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable( );
           da.Fill(dt);
           for (int i = 0; i < 5; i++)
           {
               DataRow row = dt.Rows[i];
               Console.WriteLine(i);
               Console.WriteLine(row[0]);
               Console.WriteLine(row["FirstName"]);
               Console.WriteLine(row[2, DataRowVersion.Default]);
           }
       }
   }</source>

Get first index in a row

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable( );
           da.Fill(dt);
           for (int i = 0; i < 5; i++)
           {
               DataRow row = dt.Rows[i];
               Console.WriteLine(i);
               Console.WriteLine(row[0]);
               Console.WriteLine(row["FirstName"]);
               Console.WriteLine(row[2, DataRowVersion.Default]);
           }
       }
   }</source>

Get value from a row with default value

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable( );
           da.Fill(dt);
           for (int i = 0; i < 5; i++)
           {
               DataRow row = dt.Rows[i];
               Console.WriteLine(i);
               Console.WriteLine(row[0]);
               Console.WriteLine(row["FirstName"]);
               Console.WriteLine(row[2, DataRowVersion.Default]);
           }
       }
   }</source>

Using foreach statement with DataRow in DataTable

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
           SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
           DataTable dt = new DataTable( );
           da.Fill(dt);
           int j = 0;
           foreach (DataRow row in dt.Rows)
           {
               j++;
               Console.WriteLine("Row = {0}\tContactID = {1}\tFirstName = {2}\tLastName = {3}", j, row[0], row["FirstName"],row["LastName", DataRowVersion.Default]);
           }
       }
   }</source>