SqlCommand命令类

SqlCommand类常用成员

一、属性

1、CommandText

2、CommandTimeout

3、CommandType

4、Connection

5、Parameters

6、Transaction

二、常用方法

ExecuteNonQuery()：执行SQL语句，通常为不返回数据集的操作：如update、Insert、Delete，返回被影响的数据记录的条数。int count=cmd.ExecuteNonQuery();

ExecuteReader()：返回只读向前的数据读取器，通常为select所用。

ExecuteScalar()：执行一个查询操作，同时返回查询所返回的结果集中的第一行的第一列数据，忽略其他列或行。

三、例子分析：

 【例子1】 string strSql = "Data Source=VQJREZV7DVSK2QA;Initial Catalog=gridviewAPP;User ID=sa;Password=admin@123456";
            SqlConnection conn = new SqlConnection(strSql);
            conn.Open();
            string sqlInsert = "insert into userInfo(userName,sex,address)" + " values('" + name + "','" + sex + "','" + address + "')";
            SqlCommand com = new SqlCommand(sqlInsert,conn);
            com.ExecuteNonQuery();

【例子2】 string strSql = "Data Source=VQJREZV7DVSK2QA;Initial Catalog=gridviewAPP;User ID=sa;Password=admin@123456";
            SqlConnection conn = new SqlConnection(strSql);
            conn.Open();
            SqlCommand com = new SqlCommand();

            com.CommandText="insert into userInfo(userName,sex,address)" + " values('" + name + "','" + sex + "','" + address + "')";
            int rowCount = com.ExecuteNonQuery();

【例子3】通过SqlConnection.CreateCommand()方法创建。

        SqlConnection Conn = new SqlConnection(strConn);
        string strSql = "SELECT * FROM Categories";
        SqlCommand myCommand = Conn.CreateCommand();
        myCommand.CommandText = strSql;

*****************************************************************************************************************************************************************

【实例应用】：

【1】初始化SqlCommand类的新实例

        protected void Button1_Click(object sender, EventArgs e)
        {
            string conString = "data source=127.0.0.1;initial catalog=codematic;user id=sa;password=";
            SqlConnection myConnection = new SqlConnection(conString);

            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = myConnection;     //链接字符串
            myCommand.CommandText = "update P_Product set Name='电脑1' where Id=52";//查询命令文本

            myConnection.Open();
            int rows = myCommand.ExecuteNonQuery();
            myConnection.Close();

        }

【2】初始化具有查询文本的SqlCommand类的新实例

        protected void Button2_Click(object sender, EventArgs e)
        {
            string conString = "data source=127.0.0.1;initial catalog=codematic;user id=sa;password=";
            SqlConnection myConnection = new SqlConnection(conString);
            string strSql = "update P_Product set Name='电脑2' where Id=52";

            SqlCommand myCommand = new SqlCommand(strSql);
            myCommand.Connection = myConnection;

            myConnection.Open();
            int rows = myCommand.ExecuteNonQuery();
            myConnection.Close();
        }

【3】初始化具有查询文本和SqlConnection的SqlCommand类的实例

        protected void Button3_Click(object sender, EventArgs e)
        {
            string conString = "data source=127.0.0.1;initial catalog=codematic;user id=sa;password=";
            SqlConnection myConnection = new SqlConnection(conString);
            string strSql = "update P_Product set Name='电脑3' where Id=52";

            SqlCommand myCommand = new SqlCommand(strSql, myConnection);

            myConnection.Open();
            int rows = myCommand.ExecuteNonQuery();
            myConnection.Close();
        }

【4】初始化具有查询文本、Sqlconnection和Transaction的Sqlcommand类实例

        protected void Button4_Click(object sender, EventArgs e)
        {
            string conString = "data source=127.0.0.1;initial catalog=codematic;user id=sa;password=";
            SqlConnection myConnection = new SqlConnection(conString);

            string strSql = "update P_Product set Name='电脑4' where Id=52";
            string strSql2 = "update P_Product set Name='数码4' where Id=53";

            myConnection.Open();

            SqlTransaction myTrans = myConnection.BeginTransaction();
            SqlCommand myCommand = new SqlCommand(strSql, myConnection, myTrans);

            try
            {
                int rows = myCommand.ExecuteNonQuery();
                myCommand.CommandText = strSql2;
                rows = myCommand.ExecuteNonQuery();
                myTrans.Commit();
                myConnection.Close();
            }
            catch
            {
                myTrans.Rollback();
            }
        }

【5】建立Sqlcommand和Sqlconnection的关联

        protected void Button5_Click(object sender, EventArgs e)
        {
            string conString = "data source=127.0.0.1;initial catalog=codematic;user id=sa;password=";
            SqlConnection myConnection = new SqlConnection(conString);

            SqlCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "update P_Product set Name='电脑5' where Id=52";

            myConnection.Open();
            int rows = myCommand.ExecuteNonQuery();
            myConnection.Close();
        }