Csharp/CSharp Tutorial/ADO.Net/SqlParameter
Содержание
Add SqlParameter to SqlCommand
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; public class MainClass {
[STAThread] static void Main() { string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"; using ( SqlConnection conn = new SqlConnection( cstr ) ) { conn.Open(); string selstr = "select FirstName from Employee where lastname = @name"; SqlCommand cmd = new SqlCommand( selstr, conn ); SqlParameter name = cmd.Parameters.Add( "@name", SqlDbType.NVarChar, 15 ); name.Value = "Tang"; SqlDataReader rdr = cmd.ExecuteReader(); if ( rdr.Read() ) { Console.WriteLine(rdr.GetString( 0 ) ); } else { Console.WriteLine("not available yet" ); } } }
}</source>
Command Parameter
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class CommandParameters { static void Main() { string fname = "Z"; string lname = "Z"; SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind"); string sqlqry = @"select count(*) from employees"; string sqlins = @"insert into employees(firstname,lastname)values(@fname, @lname)"; string sqldel = @"delete from employees where firstname = @fname and lastname = @lname"; SqlCommand cmdqry = new SqlCommand(sqlqry, conn); SqlCommand cmdnon = new SqlCommand(sqlins, conn); cmdnon.Prepare(); cmdnon.Parameters.Add("@fname", SqlDbType.NVarChar, 10); cmdnon.Parameters.Add("@lname", SqlDbType.NVarChar, 20); try { conn.Open(); Console.WriteLine("Before INSERT: Number of employees {0}\n", cmdqry.ExecuteScalar()); cmdnon.Parameters["@fname"].Value = fname; cmdnon.Parameters["@lname"].Value = lname; Console.WriteLine("Executing statement {0}", cmdnon.rumandText); cmdnon.ExecuteNonQuery(); Console.WriteLine("After INSERT: Number of employees {0}\n", cmdqry.ExecuteScalar()); cmdnon.rumandText = sqldel; Console.WriteLine("Executing statement {0}", cmdnon.rumandText); cmdnon.ExecuteNonQuery(); Console.WriteLine("After DELETE: Number of employees {0}\n", cmdqry.ExecuteScalar()); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { conn.Close(); Console.WriteLine("Connection Closed."); } } }</source>
Passing a Null Value to a Query Parameter
<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;"; using (SqlConnection connection = new SqlConnection(sqlConnectString)) { SqlCommand command =new SqlCommand("PassNullParameter", connection); command.rumandType = CommandType.StoredProcedure; command.Parameters.Add("@ValueIn", SqlDbType.Int); command.Parameters[0].Value = 1; connection.Open( ); Console.WriteLine(command.ExecuteScalar( )); command.Parameters[0].Value = null; Console.WriteLine("Parameter value = null"); try{ Console.WriteLine(Convert.ToBoolean(command.ExecuteScalar( ))); }catch (Exception ex){ Console.WriteLine("ERROR: {0}", ex.Message); } command.Parameters[0].Value = System.DBNull.Value; Console.WriteLine(command.ExecuteScalar( )); } } }</source>
Pass value to SqlCommand with SqlParameter
<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>