Csharp/CSharp Tutorial/ADO.Net/DataRow
Содержание
- 1 Accessing Data Values in a DataRow Array by column name
- 2 Accessing Data Values in a DataRow Array by column name and return default value
- 3 Accessing Data Values in a DataRow Array by index
- 4 Accessing Data Values in a DataRow Array in a generic way by index
- 5 Accessing Data Values in a DataRow Array in a generic way by name
- 6 Create and fill the DataRow array
- 7 DataRowState manipulation
- 8 DataRow Update
- 9 DataView and DataRowView
- 10 Filling a DataTable from the DataRow array using CopyToDataTable()
- 11 Filling a DataTable from the DataRow array using CopyToDataTable(DataTable, LoadOption)
- 12 For loop over DataRow array
- 13 Get column value in a row
- 14 Get first index in a row
- 15 Get value from a row with default value
- 16 Using foreach statement with DataRow in DataTable
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>