收录的 SqlDataReader,ExecuteNonQuery ,ExecuteScalar,DataSet 例子 - 明哥哥

公告

收录的 SqlDataReader,ExecuteNonQuery ,ExecuteScalar,DataSet 例子

1.SqlDataReader 例子程序

View Code

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

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

        private void Form1_Load(object sender, EventArgs e)
        {
            //定义输出消息
            string message = "";

            //新建连接对象
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=(local);Initial catalog=students;user id=sa;password=1234";

            //拼接命令字符串
            string selectQuery =
                "select ID,sName,sGrade,sSex from student";

            //新建命令对象
            SqlCommand cmd = new SqlCommand(selectQuery, conn);

            conn.Open();

            //关闭阅读器时将自动关闭数据库连接
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            //循环读取信息
            while (reader.Read())
            {
                message += "学号：" + reader[0].ToString() + " ";
                message += "姓名：" + reader["sName"].ToString() + " ";
                message += "班级：" + reader.GetString(2) + " ";
                message += "性别：" + reader.GetString(3) + " ";
                message += "\n";
            }

            //关闭数据阅读器
            //无需关闭连接，它将自动被关闭
            reader.Close();

            //测试数据连接是否已经关闭
            if (conn.State == ConnectionState.Closed)
            {
                message += "数据连接已经关闭\n";
            }

            MessageBox.Show(message);
        }
    }
}

2.ExecuteNonQuery 例子

View Code

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

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

        private void btnExecute1_Click(object sender, EventArgs e)
        {
            //信息检查
            if (this.CheckInfo())
            {
                //取值
                string userName = this.txtUserName.Text.Trim();
                string userID = this.txtUserID.Text.Trim();

                //新建连接对象
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString =
                    "Data Source=(local);Initial catalog=students;user id=sa;password=1234";

                //拼接命令字符串
                string updateQuery =
                    "update student set sName = '" + userName + "' " +
                    "where ID = '" + userID + "'";

                //新建命令对象
                SqlCommand cmd = new SqlCommand(updateQuery, conn);

                conn.Open();

                //保存执行结果
                int RecordsAffected = cmd.ExecuteNonQuery();

                conn.Close();

                //提示结果
                Alert("更新数据数为" + RecordsAffected);
            }
        }

        private void btnExecute2_Click(object sender, EventArgs e)
        {
            //信息检查
            if (this.CheckInfo())
            {
                //取值
                string userName = this.txtUserName.Text.Trim();
                string userID = this.txtUserID.Text.Trim();

                //新建连接对象
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString =
                    "Data Source=(local);Initial catalog=students;user id=sa;password=1234";

                //拼接命令字符串
                string updateQuery =
                    "update student set sName = @userName where ID = @userID";

                //新建命令对象
                SqlCommand cmd = new SqlCommand(updateQuery, conn);

                //添加参数
                cmd.Parameters.Add(new SqlParameter("@userName", userName));
                cmd.Parameters.Add(new SqlParameter("@userID", userID));

                conn.Open();

                //保存执行结果
                int RecordsAffected = cmd.ExecuteNonQuery();

                conn.Close();

                //提示结果
                Alert("更新数据数为" + RecordsAffected);
            }
        }

        private void BtnExecute3_Click(object sender, EventArgs e)
        {
            //信息检查
            if (this.CheckInfo())
            {
                //取值
                string userName = this.txtUserName.Text.Trim();
                string userID = this.txtUserID.Text.Trim();

                //新建连接对象
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString =
                    "Data Source=(local);Initial catalog=students;user id=sa;password=1234";

                //新建命令对象
                SqlCommand cmd = new SqlCommand("UpdateStudentInfo", conn);

                //指定命令类型为存储过程
                cmd.CommandType = CommandType.StoredProcedure;

                //添加参数
                cmd.Parameters.Add(new SqlParameter("@userName", userName));
                cmd.Parameters.Add(new SqlParameter("@userID", userID));

                conn.Open();

                //保存执行结果
                int RecordsAffected = cmd.ExecuteNonQuery();

                conn.Close();

                //提示结果
                Alert("更新数据数为" + RecordsAffected);
            }
        }

        bool CheckInfo()
        {
            //判断学号是否输入
            if (this.txtUserID.Text.Trim() == "")
            {
                Alert("学号不完整");
                return false;
            }
            //判断姓名是否输入
            else if (this.txtUserName.Text.Trim() == "")
            {
                Alert("姓名不完整");
                return false;
            }

            //信息检查通过
            return true;
        }

        /// <summary>
        /// 弹出提示信息对话框
        /// </summary>
        /// <param name="message">要提示的信息</param>
        void Alert(string message)
        {
            MessageBox.Show(null, message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}

3.ExecuteScalar例子

View Code

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

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

        private void btnGetCount_Click(object sender, EventArgs e)
        {
            //定义命令文本
            string commandText = "select count(*) from student";

            //定义连接字符串
            string connString =
                "Data Source=(local);Initial Catalog=students;User id=sa;password=1234";

            //定义Connection对象
            SqlConnection conn = new SqlConnection();

            //设置Connection对象的ConnectionString属性
            conn.ConnectionString = connString;

            //新建Command对象，此时conn对象并不需要打开连接
            SqlCommand cmd = new SqlCommand(commandText, conn);

            //打开连接
            conn.Open();

            //执行命令，返回结果
            string count = cmd.ExecuteScalar().ToString();

            //记得关闭连接
            conn.Close();

            //显示信息
            this.lblResult.Text = "共有" + count + "位学生！";
        }
    }
}

4.dataset 测试例子

View Code

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

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

        private void Form1_Load(object sender, EventArgs e)
        {
            string message = "";

            // 在此处放置用户代码以初始化页面
            //新建连接对象
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
                "Data Source=(local);Initial catalog=students;user id=sa;password=1234";

            //拼接命令字符串
            string selectQuery =
                "select ID,sName,sGrade,sSex from student";

            //新建命令对象
            SqlCommand cmd = new SqlCommand(selectQuery, conn);

            //新建数据适配器对象
            SqlDataAdapter da = new SqlDataAdapter();

            //指定数据适配器对象的Select属性
            da.SelectCommand = cmd;

            //新建DataSet对象
            DataSet result = new DataSet();

            //用数据适配器填充DataSet
            da.Fill(result, "student");

            //循环读取数据
            for (int i = 0; i < result.Tables["student"].Rows.Count; i++)
            {
                message += "学号：" + result.Tables[0].Rows[i]["ID"] + " ";
                message += "姓名：" + result.Tables[0].Rows[i]["sName"] + " ";
                message += "班级：" + result.Tables[0].Rows[i]["sGrade"] + " ";
                message += "性别：" + result.Tables[0].Rows[i]["sSex"] + " ";
                message += "\n";
            }

            MessageBox.Show(message);
        }
    }
}

posted on 2012-04-05 13:25 明哥哥阅读(211) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部