Csharp/C Sharp/Database ADO.net/Insert — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 11:42, 26 мая 2010
Содержание
Bind parameters to insert command
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();
}
}
}
Insert Data Using SqlCommandBuilder
/*
* 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);
}
}
}
Inserte Data Using SQL Statements
/*
* 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();
}
}
}
Run an INSERT statement with parameters
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();
}
}
Use SQL command to insert data into database table
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.");
}
}
}