Csharp/C Sharp by API/System.Data.SqlClient/SqlParameter
Содержание
new SqlParameter
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args) { SqlConnection cn = new SqlConnection("Data Source=(local); Initial Catalog = MyDatabase; User ID=sa;Password="); SqlCommand cmd = new SqlCommand("MyStoredProcedure", cn); cmd.rumandType = CommandType.StoredProcedure; SqlParameter param = new SqlParameter("@ReturnValue", SqlDbType.Int); cmd.Parameters.Add(param); cmd.Parameters.Add("MyFirstParameter", SqlDbType.Int); cmd.Parameters.Add("MySecondParameter", SqlDbType.Int).Direction = ParameterDirection.Output; SqlDataAdapter da = new SqlDataAdapter(cmd); }
}
</source>
SqlParameter.ParameterName
<source lang="csharp">
using System; using System.Data; using System.Data.SqlClient; class MainClass {
public static void Main() { using (SqlConnection con = new SqlConnection()) { con.ConnectionString = @"Data Source = .\sqlexpress;Database = Northwind; Integrated Security=SSPI"; con.Open(); string employeeID = "5"; string title = "Cleaner"; using (SqlCommand com = con.CreateCommand()) { com.rumandType = CommandType.Text; com.rumandText = "UPDATE Employee SET Title = @title" + " WHERE Id = @Employeeid"; // Create a SqlParameter object for the title parameter. SqlParameter p1 = com.CreateParameter(); p1.ParameterName = "@title"; p1.SqlDbType = SqlDbType.VarChar; p1.Value = title; com.Parameters.Add(p1); // Use a shorthand syntax to add the id parameter. com.Parameters.Add("@Employeeid", SqlDbType.Int).Value = employeeID; // Execute the command and process the result. int result = com.ExecuteNonQuery(); if (result == 1) { Console.WriteLine("Employee {0} title updated to {1}.", employeeID, title); } else { Console.WriteLine("Employee {0} title not updated.", employeeID); } } } }
}
</source>
SqlParameter.SourceVersion
<source lang="csharp">
using System; using System.Data; using System.Data.SqlClient;
class PropagateChanges { static void Main(){ string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; string qry = @"select * from employee "; string upd = @"update employee set firstname = @firstname where id = @id"; 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"]; dt.Rows[0]["firstname"] = "W"; 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,15, "firstname"); SqlParameter parm = cmd.Parameters.Add("@id",SqlDbType.Int,4,"id"); parm.SourceVersion = DataRowVersion.Original; da.UpdateCommand = cmd; da.Update(ds, "employee"); } catch(Exception e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); } } } </source>
SqlParameter.Value
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class MainClass {
public static void Main() { using (SqlConnection con = new SqlConnection()) { con.ConnectionString = @"Data Source = .\sqlexpress;Database = Northwind; Integrated Security=SSPI"; con.Open(); string employeeID = "5"; string title = "Cleaner"; using (SqlCommand com = con.CreateCommand()) { com.rumandType = CommandType.Text; com.rumandText = "UPDATE Employee SET Title = @title" + " WHERE Id = @Employeeid"; // Create a SqlParameter object for the title parameter. SqlParameter p1 = com.CreateParameter(); p1.ParameterName = "@title"; p1.SqlDbType = SqlDbType.VarChar; p1.Value = title; com.Parameters.Add(p1); // Use a shorthand syntax to add the id parameter. com.Parameters.Add("@Employeeid", SqlDbType.Int).Value = employeeID; // Execute the command and process the result. int result = com.ExecuteNonQuery(); if (result == 1) { Console.WriteLine("Employee {0} title updated to {1}.", employeeID, title); } else { Console.WriteLine("Employee {0} title not updated.", employeeID); } } } }
}
</source>