ADO.NET基础学习-----四种模型,防止SQL注入

1.ExcuteNonQuery

　　执行非查询语句，返回受影响的行数。

           // 1.ExcuteNonQuery

            string sqlconn = "Data Source=wss;Initial Catalog=TextDB;User ID=sa;Password=w778764;Integrated Security=true";
            SqlConnection conn = new SqlConnection(sqlconn);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "insert into TB_USER(userID,password,LastTime,code) values ('fungame','131313',2005-05-23,56)";
            int num = cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
            Console.Write("(共有{0}行受影响",num.ToString());
            Console.ReadKey();

---

 

2.ExcuteScalar

　　执行查询语句，返回一行一列的数据。

            2.ExcuteScaler
            string sqlconn= "data source = wss;uid = sa;pwd = w778764;Integrated Security = true;Initial Catalog = TextDB;";
            string sql = "select * from TB_USER ";
            using (SqlConnection conn = new SqlConnection (sqlconn))
            {
                using (SqlCommand cmd = new SqlCommand(sql,conn))
                {
                    conn.Open();
                    string str = cmd.ExecuteScalar().ToString();
                    Console.WriteLine(str);
                    conn.Close();
                }
            }
            Console.ReadKey();

---

3.参数化查询（防止SQL注入<1' or '1' = '1>）

            //3.防止SQL注入，参数化查询
            Console.WriteLine("请输入用户名：");
            string uid =Console.ReadLine();
            Console.WriteLine("请输入密码");
            string pwd = Console.ReadLine();

            string sqlconn = "data source = wss;uid = @uid;pwd = @pwd;Integrated Security = true;Initial Catalog = TextDB;";
            string sqldtr = "select count(*) from TB_USER;";

            SqlParameter spuid = new SqlParameter(@uid, uid);//构建参数化对象
            SqlParameter sppwd = new SqlParameter(@pwd, pwd);

            using (SqlConnection conn = new SqlConnection(sqlconn))
            {
                using (SqlCommand cmd = new SqlCommand(sqldtr,conn))
                {
                    
                    cmd.Parameters.Add(spuid);//将参数对象添加入执行列表
                    cmd.Parameters.Add(sppwd);
                    conn.Open();
                    int num =(int) cmd.ExecuteScalar();
                    conn.Close();
                    Console.Write("n={0}", num);
                }
            }

---

4.sqldatareader

　　执行查询语句，将数据暂存到sqldatareader对象中，可以通过索引将数据逐行取出。

            4.sqldatareader使用
            string sqlstr = "data source = wss;uid =@uid;pwd =@pwd;Initial Catalog = TextDB;Integrated Security = true;";
            string sql = "select * from TB_USER;";
            string uid = "12";
            string pwd = "123";
            SqlParameter suid = new SqlParameter(@uid, uid);
            SqlParameter spwd = new SqlParameter(@pwd, pwd);
            using (SqlConnection conn = new SqlConnection (sqlstr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.Add(suid);
                    cmd.Parameters.Add(spwd);
                    conn.Open();
                    SqlDataReader sqlread = cmd.ExecuteReader();
                    using (sqlread)
                    {
                        while(sqlread.Read())
                        {
                            for (int i = 0; i < sqlread.FieldCount; i++)
                            {
                                string data = sqlread[i].ToString();
                                Console.Write(data+"\t");
                            }
                            Console.WriteLine();
                        }
                    }
                    conn.Close();
                }
            }

---

5.SqlDataAdapter

　　通过 DataAdapter 使用数据源中的数据生成和填充 DataSet 中的每个 DataTable，可以通过索引将单个的datatable数据绑定到数据容器中显示，如：datagridview。

　　该实例中是建立了winform程序，设计视图中添加一个gridview和按钮控件，在按钮单击事件中添加如下代码。tips:girdview属性中的Anchor（锚；抛锚停泊）可以设定控件的四个方向是否跟随窗口大小改动而改动。

private void button1_Click(object sender, EventArgs e)
        {
            string sqlstr = "data source = wss;uid =@uid;pwd =@pwd;Initial Catalog = TextDB;Integrated Security = true;";
            string sql = "select * from TB_USER;";
            DataSet ds = new DataSet();
            using (SqlDataAdapter sda = new SqlDataAdapter(sql,sqlstr))
            {
                sda.Fill(ds);
            }
            dataGridView1.DataSource = ds.Tables[0];
        }

 如果这些内容帮助到你了，希望您给予支持 o(^▽^)o （支付宝）

 

（支付宝）