加载中…
个人资料
小宇
小宇
  • 博客等级:
  • 博客积分:0
  • 博客访问:101,544
  • 关注人气:20
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

数据库类的封装使用示例

(2013-07-29 16:48:11)
标签:

it

分类: 软件技术

数据库类的封装使用示例

一、界面

数据库类的封装使用示例

二、代码:

 1、数据库类:

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

namespace 数据库类的封装
{
    class SQLHelper
    {
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)//static表示静态类,可以直接引用,不用new. params SqlParameter[] parameters 表示可以传多个参数.格式为ExecuteNonQuery("insert into table(f1,f2) values(@f1,@f2)",p1,p2)
        {
            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//VS2005下
            //string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)//将参数值遍历添加进去
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteNonQuery();
                }

            }

        }

        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//VS2005下
            //string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)//将参数值遍历添加进去
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteScalar();
                }

            }
        }

        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)//这个方法是错误的,不能使用
        {
            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//VS2005下
            //string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    foreach (SqlParameter parameter in parameters)//将参数值遍历添加进去
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteReader();
                }

            }
        }

        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
          string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//VS2005下
          using (SqlConnection conn = new SqlConnection(connStr))
          {
              conn.Open();
              using (SqlCommand cmd = conn.CreateCommand())
              {
                  cmd.CommandText = sql;
                  foreach (SqlParameter parameter in parameters)
                  {
                      cmd.Parameters.Add(parameter);
                  }
                  DataSet dataset = new DataSet();
                  SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                  adapter.Fill(dataset);
                  return dataset.Tables[0];
              }
          }
        }

    }
}

2、界面调用代码:

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 数据库类的封装
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SQLHelper.ExecuteNonQuery("insert into T_Persons(Name,Age) values(@Name,@Age)", new SqlParameter("Name", "tom"), new SqlParameter("Age", 30));
        }

        private void button2_Click(object sender, EventArgs e)
        {
            object i = SQLHelper.ExecuteScalar("select count(*) from T_Persons");
            MessageBox.Show(Convert.ToString(i));
        }

        private void button3_Click(object sender, EventArgs e)
        {
            SqlDataReader reader = SQLHelper.ExecuteReader("select * from T_Persons");
            while (reader.Read())
            {
                string name = reader.GetString(reader.GetOrdinal("Name"));
                MessageBox.Show(name);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//VS2005下
            //string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from T_Persons";
                    DataSet dataset = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dataset);
                    DataTable table = dataset.Tables[0];
                    for (int i = 0; i < table.Rows.Count; i++) //遍历表中每一行
                    {
                        DataRow row = table.Rows[i];//从第0行开始
                        string name = Convert.ToString(row["Name"]);//获取这一行字段名为"Name"的值
                        MessageBox.Show(name);
                    }
                }
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            DataTable dt = SQLHelper.ExecuteDataTable("select * from T_Persons");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow row = dt.Rows[i];
                string name = Convert.ToString(row["Name"]);
                MessageBox.Show(name);
            }
        }

        private void btnLogin_Click(object sender, EventArgs e)
        {
            DataTable dt = SQLHelper.ExecuteDataTable("select * from T_Users where UserName=@UserName", new SqlParameter("UserName", txtUserName.Text));
            if (dt.Rows.Count <= 0)
            {
                MessageBox.Show("用户名不存在!");
                return;
            }
            else
            {
                DataRow row = dt.Rows[0];//表的第一行
                int errorTimes=Convert.ToInt32(row["ErrorTimes"]);
                if (errorTimes>=3)
                {
                  MessageBox.Show("登陆次数太多!");
                  return;
                }
                string dbpassword = Convert.ToString(row["Password"]);
                if (dbpassword == txtPassword.Text)
                {
                    MessageBox.Show("登陆成功");
                    SQLHelper.ExecuteNonQuery("update T_Users Set ErrorTimes=0 where UserName=@UserName", new SqlParameter("UserName", txtUserName.Text));
                }
                else
                {
                  SQLHelper.ExecuteNonQuery("update T_Users Set ErrorTimes=ErrorTimes+1 where UserName=@UserName",new SqlParameter("UserName",txtUserName.Text));
                  MessageBox.Show("错误");
                }
            }

 

        private void button6_Click(object sender, EventArgs e)//DATASet的更新方法
        {
            DataSet dataset = new DataSet();
            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//VS2005下

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from T_Persons";
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dataset);
                    DataTable table = dataset.Tables[0];
                    DataRow row = table.Rows[0];
                    row["Name"] = "jason";
                    //table.Rows.RemoveAt(1);//把第一行删掉
                    //table.NewRow();//新增加一行
                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);//这句话是重点,注意使用SqlCommandBuilder时,SqlDataAdapter的查询语句中必须包含数据库主键的查询信息,否则会报错.
                   
                    adapter.Update(dataset);//在执行更新前,需要指定command命令.


                }
            }

        }
    }
}

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有