Csharp/C Sharp/Database ADO.net/Insert
Содержание
Bind parameters to insert command
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient;
class PropagateAdds{ static void Main() { string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string qry = @"select * from employee"; string upd = @"insert into employee(firstname,lastname)values(@firstname,@lastname)"; SqlConnection conn = new SqlConnection(connString); try{ SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new SqlCommand(qry, conn); DataSet ds = new DataSet(); da.Fill(ds, "employee"); DataTable dt = ds.Tables["employee"]; DataRow newRow = dt.NewRow(); newRow["firstname"] = "R"; newRow["lastname"] = "B"; dt.Rows.Add(newRow); foreach (DataRow row in dt.Rows){ Console.WriteLine( "{0} {1}", row["firstname"].ToString().PadRight(15), row["lastname"].ToString().PadLeft(25)); } // Update employees SqlCommand cmd = new SqlCommand(upd, conn); cmd.Parameters.Add("@firstname", SqlDbType.NVarChar, 10, "firstname"); cmd.Parameters.Add("@lastname", SqlDbType.NVarChar, 20, "lastname"); da.InsertCommand = cmd; da.Update(ds, "employee"); } catch(Exception e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); } } }
</source>
Insert Data Using SqlCommandBuilder
<source lang="csharp"> /*
* C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */
using System; using System.Data; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET {
public class InsertingDataUsingCommandBuilder { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT ID, Contact, Email FROM Test", MyConnection); SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter); DataSet MyDataSet = new DataSet(); MyDataAdapter.Fill(MyDataSet); DataRow MyRow = MyDataSet.Tables[0].NewRow(); MyRow["ID"] = 200; MyRow["Contact"] = "Greg"; MyRow["Email"] = "MacBeth"; MyDataSet.Tables[0].Rows.Add(MyRow); MyDataAdapter.Update(MyDataSet); } }
}
</source>
Inserte Data Using SQL Statements
<source lang="csharp"> /*
* C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */
using System; using System.Data; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET {
public class InsertingDataUsingSQLStatements { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); MyConnection.Open(); String MyString = @"INSERT INTO Test(ID, Contact, Email) VALUES(2, "Greg", "MacBeth")"; SqlCommand MyCmd = new SqlCommand(MyString, MyConnection); MyCmd.ExecuteScalar(); MyConnection.Close(); } }
}
</source>
Run an INSERT statement with parameters
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class UsingParameters {
public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); mySqlConnection.Open(); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.rumandText = "INSERT INTO Customers (" + " CustomerID, CompanyName, ContactName" + ") VALUES (" + " @CustomerID, @CompanyName, @ContactName" + ")"; mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); mySqlCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40); mySqlCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30); mySqlCommand.Parameters["@CustomerID"].Value = "J4COM"; mySqlCommand.Parameters["@CompanyName"].Value = "J4 Company"; mySqlCommand.Parameters["@ContactName"].IsNullable = true; mySqlCommand.Parameters["@ContactName"].Value = DBNull.Value; mySqlCommand.ExecuteNonQuery(); Console.WriteLine("Successfully added row to Customers table"); mySqlConnection.Close(); }
}
</source>
Use SQL command to insert data into database table
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient;
class CommandExampleCreateDb { static void Main() { SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlCommand nonqueryCommand = thisConnection.CreateCommand(); try { thisConnection.Open(); nonqueryCommand.rumandText = "CREATE DATABASE MyDb"; Console.WriteLine(nonqueryCommand.rumandText); nonqueryCommand.ExecuteNonQuery(); Console.WriteLine("Database created, now switching"); thisConnection.ChangeDatabase("MyDb"); nonqueryCommand.rumandText = "CREATE TABLE MynfexTable (COL1 integer)"; Console.WriteLine(nonqueryCommand.rumandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); nonqueryCommand.rumandText = "INSERT INTO MynfexTable VALUES (99)"; Console.WriteLine(nonqueryCommand.rumandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } }
</source>