SqlHelper 和 SqlAdapter SqlCommand 查询、添加、修改、删除

 public class SQLHelper
    {
        //数据库连接串
        public static string SGLZConnectionString = System.Configuration.ConfigurationSettings.AppSettings["SQLConnString"];

        // Hashtable to store cached parameters
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

    
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

    
        public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

     
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

      
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);

            // we use a try/catch here because if the method throws an exception we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

  
        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

    
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

   
        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

   
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

      
    }

 

 

 

经常用到的对象有：SqlConnection，SqlAdapter，SqlCommand、Dataset、DataGrid和DataReader等，以SqlConnection，SqlAdapter，SqlCommand、Dataset、DataGrid对象，操作SQL的实例数据库Northwind中的Categorys表为例说明（假定数据库在本地，数据库访问的用户名为user，密码为123456）：
首先引入数据库操作引用：
using System.Data.SqlClient;

第一、查询数据：
string MyConn="server=127.0.0.1;uid=user;pwd=123456;database=Northwind;Trusted_Connection=no";//定义数据库连接参数
SqlConnection MyConnection=new SqlConnection(MyConn);//定义一个数据连接实例
SqlCommand MyCommand=new SqlCommand("SELECT CategoryID, CategoryName, Description FROM Categories",MyConnection); //定义一个数据库操作指令
SqlDataAdapter SelectAdapter=new SqlDataAdapter();//定义一个数据适配器
SelectAdapter.SelectCommand=MyCommand;//定义数据适配器的操作指令
DataSet MyDataSet=new DataSet();//定义一个数据集
MyConnection.Open();//打开数据库连接
SelectAdapter.SelectCommand.ExecuteNonQuery();//执行数据库查询指令
MyConnection.Close();//关闭数据库
SelectAdapter.Fill(MyDataSet);//填充数据集
DataGrid1.DataSource=MyDataSet;
DataGrid1.DataBind();//将数据表格用数据集中的数据填充

 

 void FillData()
        {
            // 1 Open connection
            using (SqlConnection c = new SqlConnection( Properties.Settings.Default.DataConnectionString))
            {
                c.Open();
                // 2 Create new DataAdapter
                using (SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM EmployeeIDs", c))
                {
                    // 3 Use DataAdapter to fill DataTable
                    DataTable t = new DataTable();
                    a.Fill(t);

                    // 4
                    // Render data onto the screen
                    // dataGridView1.DataSource = t; // <-- From your designer
                }
            }
        }


第二、添加数据

string MyConn="server=127.0.0.1;uid=user;pwd=123456;database=Northwind;Trusted_Connection=no";
SqlConnection MyConnection=new SqlConnection(MyConn);
string MyInsert="insert into Categories(CategoryName, Description)values('"+Convert.ToString(TextBox2.Text)+"','"+Convert.ToString(TextBox3.Text)+"')";
SqlCommand MyCommand=new SqlCommand(MyInsert,MyConnection);
try//异常处理
{
MyConnection.Open();
MyCommand.ExecuteNonQuery();
MyConnection.Close();
}
catch(Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex);
}

第三、修改数据

string categoryName=TextBox2.Text;
string categoryDescription=TextBox3.Text;
string MyConn="server=127.0.0.1;uid=user;pwd=123456;database=Northwind;Trusted_Connection=no";
SqlConnection MyConnection=new SqlConnection(MyConn);
string MyUpdate="Update Categories set CategoryName='"+categoryName+"',Description='"+categoryDescription+"' where CategoryID="+TextBox1.Text;
SqlCommand MyCommand=new SqlCommand(MyUpdate,MyConnection);
try
{
MyConnection.Open();
MyCommand.ExecuteNonQuery();
MyConnection.Close();
TextBox1.Text="";
}
catch(Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex);
}

第四、删除数据

string MyConn="server=127.0.0.1;uid=user;pwd=123456;database=Northwind;Trusted_Connection=no";
SqlConnection MyConnection=new SqlConnection(MyConn);
string MyDelete="Delete from Categories where CategoryID="+TextBox1.Text;
SqlCommand MyCommand=new SqlCommand(MyDelete,MyConnection);
try
{
MyConnection.Open();
MyCommand.ExecuteNonQuery();
MyConnection.Close();
TextBox1.Text="";
}
catch(Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex);
}

 

 

SqlConnection con = new SqlConnection("连接字符串"); // 打开数据库连接 con.Open(); SqlCommand cmd = new SqlCommand("SELECT * FROM 表名 WHERE ID=@ID", con); // 添加参数 cmd.Parameters.Add(new SqlParameter("@ID", "值")); // 如果是insert语句,执行ExecuteNonQuery()方法，此方法返回受影响的行数 cmd.ExecuteNonQuery(); // 如果是执行查询返回单个值，执行ExecuteScalar()方法，此方法返回第一行第一列值，返回的是object cmd.ExecuteScalar(); // 如果执行查询返回多个值 SqlDataReader dr = cmd.ExecuteReader(); // 两种方法读取值 // 1. DataTable dt = new DataTable(); // 将值读取到DataTable中 dt.Load(dr); // 2. while (dr.Read()) { object a = dr[0]; } // 关闭sqlDataReader dr.Close(); // 关闭连接 con.Close();

 

 

 C# codeusing System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace MyBookShopDAL{ public abstract class DBHelper { private static readonly string connString = ConfigurationManager.ConnectionStrings["S2_MyBookShopConnectionString"].ConnectionString; public static DataSet ExecuteDataSet(string sql) { SqlDataAdapter sda = new SqlDataAdapter(sql,connString); DataSet ds = new DataSet(); sda.Fill(ds,"MySchoolShop"); return ds; } public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] ps) { SqlConnection connection = new SqlConnection(connString); SqlCommand command = new SqlCommand(sql,connection); command.CommandType = type; if (ps != null) command.Parameters.AddRange(ps); connection.Open(); int i = command.ExecuteNonQuery(); connection.Close(); return i; } public static SqlDataReader ExecuteDataReader(string sql, CommandType type,params SqlParameter[] ps) { SqlConnection connection = new SqlConnection(connString); SqlCommand command = new SqlCommand(sql, connection); command.CommandType = type; if (ps != null) command.Parameters.AddRange(ps); connection.Open(); return command.ExecuteReader(CommandBehavior.CloseConnection); } public static object ExecuteSalcue(string sql, CommandType type, params SqlParameter[] ps) { SqlConnection connection = new SqlConnection(connString); SqlCommand command = new SqlCommand(sql, connection); command.CommandType = type; if (ps != null) command.Parameters.AddRange(ps); connection.Open(); object obj = command.ExecuteScalar(); connection.Close(); return obj; } }}