参数化查询

 (一)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class UserService
    {

        /// <summary>
        /// 增加的方法
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static bool Add(Users user)
        {
            string sql = "insert into T_User(ID,Fusername,Fpassword)values((select MAX(ID) from T_User)+1, @Fusername,@Fpassword)";

            //Dictionary<string, object> dic = new Dictionary<string, object>();
            //dic.Add("@Fusername",user.UserName);
            //dic.Add("@Fpassword",user.Password);

            SqlParameter[] parmeters = new SqlParameter[] {
                                        new SqlParameter("@Fusername",user.UserName),
                                        new SqlParameter("@Fpassword",user.Password)              
                                                      };

            return SqlHelper.ExecuteNonQuery(sql, parmeters)>0?true:false;
        }

        /// <summary>
        /// 删除的方法
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static bool Delete(int UserID)
        {
            //string sql = "delete from T_User where ID=@UserID";

            string sql = " update T_User set FCheck=2   where ID=@UserID ";

            SqlParameter[] parmeters = new SqlParameter[] { new SqlParameter("@UserID",UserID) };

            return SqlHelper.ExecuteNonQuery(sql, parmeters) > 0 ? true : false;
        }

        /// <summary>
        /// 修改的方法
        /// </summary>
        public static bool Modify(Users user)
        {
            string sql="update T_User set Fusername=@Fusername,Fpassword=@Fpassword where ID=@ID";
            SqlParameter[] parmeters = new SqlParameter[] {
                            new SqlParameter("@Fusername",user.UserName),
                            new SqlParameter("@Fpassword",user.Password),
                            new SqlParameter("@ID",user.UserID)
                                                           };

            return SqlHelper.ExecuteNonQuery(sql, parmeters) > 0 ? true : false;
        }

        /// <summary>
        /// 获得一个类对象Model的方法
        /// </summary>
        /// <returns></returns>
        public static List<Users> GetALLUsers()
        {
            string sql = "select ID,Fusername,Fpassword,ISCheck from T_User where FCheck=1";
            //DataTable table = SqlHelper.ExecuteDataTable(sql, null);//这里传入的参数没有的话就不需要传入参数
            DataTable table = SqlHelper.ExecuteDataTable(sql);
            List<Users> list = new List<Users>();

            foreach (DataRow dr in table.Rows)//遍历得到的table中的数据行,这里不能写成foreach (var dr in table.Rows),因为var是一个object类型
            {
                Users user = new Users();//准备一个User类对象
                user.UserID = (int)dr["ID"];
                user.UserName = dr["Fusername"].ToString();
                user.Password = dr["Fpassword"].ToString();
                list.Add(user);
            }
            return list;//返回list对象
        }

 

        /// <summary>
        /// 获得一个类对象Model的方法
        /// </summary>
        /// <returns></returns>
        public static List<Users> GetALLUsers1()
        {
            string sql = "select ID,Fusername,Fpassword from T_User where FCheck=2 ";
            //DataTable table = SqlHelper.ExecuteDataTable(sql, null);//这里传入的参数没有的话就不需要传入参数
            DataTable table = SqlHelper.ExecuteDataTable(sql);
            List<Users> list = new List<Users>();

            foreach (DataRow dr in table.Rows)//遍历得到的table中的数据行,这里不能写成foreach (var dr in table.Rows),因为var是一个object类型
            {
                Users user = new Users();//准备一个User类对象
                user.UserID = (int)dr["ID"];
                user.UserName = dr["Fusername"].ToString();
                user.Password = dr["Fpassword"].ToString();
                list.Add(user);
            }
            return list;//返回list对象
        }

 

        //通过Id获得
        public static DataTable getNameById(int userId)
        {
            string sql = "select Fusername from T_User where ID=@ID";
            SqlParameter[] parameters = new SqlParameter[] {new SqlParameter("@ID",userId) };

            DataTable table = SqlHelper.ExecuteDataTable(sql, parameters);
           
            return table;
        }

 

        //根据查询条件得到结果集
        public static DataTable getData(string strWhere)
        {
            DataTable table = new DataTable();

            table = SqlHelper.ExecuteProc("Common_GetCount", new SqlParameter("@TableName", "T_User"), new SqlParameter("@Condition",strWhere));

            return table;
                
        }

     }

}

 

 

（二）

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string sql = "select * from FRole where ID=@ID";

          
            SqlParameter[] parameter = new SqlParameter[]{ new SqlParameter("@ID",textBox1.Text)};

            DataTable table = ClassLibrary1.SqlHelper.ExecuteDataTable(sql,parameter);

            dataGridView1.DataSource = table;
        }
    }
}