/* * 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(); } } }
Database ADO.net
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.CommandText = "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(); } }
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(); } } }
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.CommandText = "CREATE DATABASE MyDb"; Console.WriteLine(nonqueryCommand.CommandText); nonqueryCommand.ExecuteNonQuery(); Console.WriteLine("Database created, now switching"); thisConnection.ChangeDatabase("MyDb"); nonqueryCommand.CommandText = "CREATE TABLE MyJava2sTable (COL1 integer)"; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); nonqueryCommand.CommandText = "INSERT INTO MyJava2sTable VALUES (99)"; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } }
Set CommandType and CommandText of IDbConnection
using System; using System.Data; using System.Data.SqlClient; class MainClass { public static void ExecuteNonQueryExample(IDbConnection con) { IDbCommand com = con.CreateCommand(); com.CommandType = CommandType.Text; com.CommandText = "UPDATE Employees SET Title = 'Sales Director' WHERE EmployeeId = '5'"; int result = com.ExecuteNonQuery(); Console.WriteLine(result); } public static void ExecuteReaderExample(IDbConnection con) { IDbCommand com = con.CreateCommand(); com.CommandType = CommandType.StoredProcedure; com.CommandText = "Ten Most Expensive Products"; using (IDataReader reader = com.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(" {0} = {1}", reader["TenMostExpensiveProducts"], reader["UnitPrice"]); } } } public static void ExecuteScalarExample(IDbConnection con) { IDbCommand com = con.CreateCommand(); com.CommandType = CommandType.Text; com.CommandText = "SELECT COUNT(*) FROM Employees"; int result = (int)com.ExecuteScalar(); Console.WriteLine("Employee count = " + result); } public static void Main() { using (SqlConnection con = new SqlConnection()) { con.ConnectionString = @"Data Source = .sqlexpress;Database = Northwind; Integrated Security=SSPI"; con.Open(); ExecuteNonQueryExample(con); ExecuteReaderExample(con); ExecuteScalarExample(con); } } }
Deleting Data
using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; class Program { static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection( @"Server=(local)sqlexpress;Integrated Security=True;" + "Database=northwind"); SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", thisConnection); SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Customers"); Console.WriteLine("# rows before change: {0}",thisDataSet.Tables["Customers"].Rows.Count); DataColumn[] keys = new DataColumn[1]; keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"]; thisDataSet.Tables["Customers"].PrimaryKey = keys; DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("ZACZI"); if (findRow != null) { findRow.Delete(); thisAdapter.Update(thisDataSet, "Customers"); } Console.WriteLine("# rows after change: {0}", thisDataSet.Tables["Customers"].Rows.Count); thisConnection.Close(); } }
Deleting Data Using SQLStatements
/* * 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 DeletingDataUsingSQLStatements { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); MyConnection.Open(); String MyString = "DELETE Test"; SqlCommand MyCmd = new SqlCommand(MyString, MyConnection); MyCmd.ExecuteScalar(); MyConnection.Close(); } } }