2023年6月21日发(作者：)
SqlParameter的作⽤与⽤法 ⼀般来说，在更新DataTable或是DataSet时，如果不采⽤SqlParameter，那么当输⼊的Sql语句出现歧义时，如字符串中含有单引号，程序就会发⽣错误，并且他⼈可以轻易地通过拼接Sql语句来进⾏注⼊攻击。1718string sql = "update Table1 set name = 'Pudding' where ID = '1'" ; //未采⽤SqlParameterSqlConnection conn = new SqlConnection();tionString = "Data Source=.SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|mmand cmd = new SqlCommand(sql, conn);try{ (); return (eNonQuery());}catch (Exception){ return -1; throw ;}finally{ ();}上述代码未采⽤SqlParameter，除了存在安全性问题，该⽅法还⽆法解决⼆进制流的更新，如图⽚⽂件。通过使⽤SqlParameter可以解决上述问题，常见的使⽤⽅法有两种，Add⽅法和AddRange⽅法。⼀、Add⽅法1234SqlParameter sp = new SqlParameter( "@name" , "Pudding" );(sp);sp = new SqlParameter( "@ID" , "1" );(sp); 该⽅法每次只能添加⼀个SqlParameter。上述代码的功能是将ID值等于1的字段name更新为Pudding(⼈名)。⼆、AddRange⽅法12SqlParameter[] paras = new SqlParameter[] { new SqlParameter( "@name" , "Pudding" ),
ge(paras); 显然，Add⽅法在添加多个SqlParameter时不⽅便，此时，可以采⽤AddRange⽅法。 下⾯是通过SqlParameter向数据库存储及读取图⽚的代码。1public int SavePhoto( string photourl)2345678910{ FileStream fs = new FileStream(photourl, , ); //创建FileStream对象，⽤于向BinaryReader写 BinaryReader br = new BinaryReader(fs); //创建BinaryReader对象，⽤于写⼊下⾯的byte数组 byte [] photo = tes(( int )); //新建byte数组，写⼊br中的数据 (); //记得要关闭br (); //还有fs string sql = "update Table1 set photo = @photo where ID = '0'" ; SqlConnection conn = new SqlConnection(); tionString = "Data Source=.SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|Databas11 SqlCommand cmd = new SqlCommand(sql, conn);12 SqlParameter sp = new SqlParameter( "@photo" , photo);13 (sp);14 try15 {16 ();17 return (eNonQuery());18 }19 catch (Exception)20 {21 return -1;22 throw ;23 }24 finally25 {26 ();27 }28}29
30public void ReadPhoto( string url)31 {32 string sql = "select photo from Table1 where ID = '0'" ;33 SqlConnection conn = new SqlConnection();34 tionString = "Data Source=.SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|Datab35 SqlCommand cmd = new SqlCommand(sql, conn);36 try37 {38 ();39 SqlDataReader reader = eReader(); //采⽤SqlDataReader的⽅法来读取数据40 if (())41 {42 byte [] photo = reader[0] as byte []; //将第0列的数据写⼊byte数组43 FileStream fs = new FileStream(url,New);创建FileStream对象，⽤于写⼊字节数据流44 (photo,0,); //将byte数组中的数据写⼊fs45 (); //关闭fs46 }47 (); //关闭reader48 }49 catch (Exception ex)50 {51 throw ;52 }53 finally54 {55 ();56 }57 }58}
2023年6月21日发(作者：)
SqlParameter的作⽤与⽤法 ⼀般来说，在更新DataTable或是DataSet时，如果不采⽤SqlParameter，那么当输⼊的Sql语句出现歧义时，如字符串中含有单引号，程序就会发⽣错误，并且他⼈可以轻易地通过拼接Sql语句来进⾏注⼊攻击。1718string sql = "update Table1 set name = 'Pudding' where ID = '1'" ; //未采⽤SqlParameterSqlConnection conn = new SqlConnection();tionString = "Data Source=.SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|mmand cmd = new SqlCommand(sql, conn);try{ (); return (eNonQuery());}catch (Exception){ return -1; throw ;}finally{ ();}上述代码未采⽤SqlParameter，除了存在安全性问题，该⽅法还⽆法解决⼆进制流的更新，如图⽚⽂件。通过使⽤SqlParameter可以解决上述问题，常见的使⽤⽅法有两种，Add⽅法和AddRange⽅法。⼀、Add⽅法1234SqlParameter sp = new SqlParameter( "@name" , "Pudding" );(sp);sp = new SqlParameter( "@ID" , "1" );(sp); 该⽅法每次只能添加⼀个SqlParameter。上述代码的功能是将ID值等于1的字段name更新为Pudding(⼈名)。⼆、AddRange⽅法12SqlParameter[] paras = new SqlParameter[] { new SqlParameter( "@name" , "Pudding" ),
ge(paras); 显然，Add⽅法在添加多个SqlParameter时不⽅便，此时，可以采⽤AddRange⽅法。 下⾯是通过SqlParameter向数据库存储及读取图⽚的代码。1public int SavePhoto( string photourl)2345678910{ FileStream fs = new FileStream(photourl, , ); //创建FileStream对象，⽤于向BinaryReader写 BinaryReader br = new BinaryReader(fs); //创建BinaryReader对象，⽤于写⼊下⾯的byte数组 byte [] photo = tes(( int )); //新建byte数组，写⼊br中的数据 (); //记得要关闭br (); //还有fs string sql = "update Table1 set photo = @photo where ID = '0'" ; SqlConnection conn = new SqlConnection(); tionString = "Data Source=.SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|Databas11 SqlCommand cmd = new SqlCommand(sql, conn);12 SqlParameter sp = new SqlParameter( "@photo" , photo);13 (sp);14 try15 {16 ();17 return (eNonQuery());18 }19 catch (Exception)20 {21 return -1;22 throw ;23 }24 finally25 {26 ();27 }28}29
30public void ReadPhoto( string url)31 {32 string sql = "select photo from Table1 where ID = '0'" ;33 SqlConnection conn = new SqlConnection();34 tionString = "Data Source=.SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|Datab35 SqlCommand cmd = new SqlCommand(sql, conn);36 try37 {38 ();39 SqlDataReader reader = eReader(); //采⽤SqlDataReader的⽅法来读取数据40 if (())41 {42 byte [] photo = reader[0] as byte []; //将第0列的数据写⼊byte数组43 FileStream fs = new FileStream(url,New);创建FileStream对象，⽤于写⼊字节数据流44 (photo,0,); //将byte数组中的数据写⼊fs45 (); //关闭fs46 }47 (); //关闭reader48 }49 catch (Exception ex)50 {51 throw ;52 }53 finally54 {55 ();56 }57 }58}
发布评论