public partial class Default2 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { SqlConnection myConnection = new SqlConnection(); myConnection.ConnectionString = "Data Source=10.225.106.27;Initial Catalog=Ticket;User ID=sa;password=password_1"; //define conn str myConnection.Open(); SqlCommand cmd = new SqlCommand("insert into dbo.Ticket values(@p1,@p2,@p3,@p4)", myConnection); //bulid command cmd.Prepare(); //prepare begin SqlParameter p1 = new SqlParameter("@p1", SqlDbType.VarChar); p1.Value = "3"; // matching and evaluate cmd.Parameters.Add(p1); SqlParameter p2 = new SqlParameter("@p2", SqlDbType.VarChar); p2.Value = "ok"; cmd.Parameters.Add(p2); SqlParameter p3 = new SqlParameter("@p3", SqlDbType.VarChar); p3.Value = "isee"; cmd.Parameters.Add(p3); SqlParameter p4 = new SqlParameter("@p4", SqlDbType.VarChar); p4.Value = "success"; cmd.Parameters.Add(p4); cmd.ExecuteNonQuery(); // execute } } //* this case haven't try & catch block,just for demonstrating prepare statement in order to avoid some sql hard to write
sqlparameter arrary as parameter receive variable
public partial class Default6 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Data Source=10.225.106.27;Initial Catalog=Ticket;User ID=sa;password=password_1"; try { conn.Open(); DataSet ds = new DataSet(); string sql = "select * from dbo.Ticket where TicketNo = @id"; SqlParameter[] prams = new SqlParameter[1]; prams[0] = new SqlParameter("@id", DbType.String); prams[0].Value = '1'; ds = Excute(conn, sql, prams); foreach (DataRow dr in ds.Tables[0].Rows) { object value = dr["IndentType"]; Response.Write(value.ToString()); } } catch (Exception) { throw; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } } } private DataSet Excute(SqlConnection conn ,string sql ,SqlParameter[] paras) { DataSet ds = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(sql,conn); if (paras != null) { foreach (SqlParameter para in paras) cmd.Parameters.Add(para); } adapter.SelectCommand = cmd; adapter.Fill(ds); return ds; } }