c# 数据库操作类Sql

数据库操作类Sql

数据库操作类的封装是任何工程都必不可少地，本例当然也不例外。Sql类主要包括两类方法：一类用来直接执行SQL语句，另一类用来执行存储过程。

其中的数据库连接字符串的数据库路径采用了绝对路径，读者调试程序时应当做相应的更改。Sql类的具体代码如下：

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Data;

namespace MyChat

{

    //数据库操作类

    public class Sql

    {

          private string str = null;                   //数据库连接字符串

          public SqlConnection Con;                    //sql数据连接组件实例化

          public SqlCommand command = new SqlCommand();    //初始化一个SQL命令对象

          public Sql()                                 //类初始化，初始化数据连接

          {

                string path = @"C:/Documents and Settings/Administrator/桌面/tools/

                mychat1.0/Chat";

                //数据库连接字符串

                str="Data Source=.//SQLEXPRESS;AttachDbFilename=/"" + path +

                "//app_data//chat.mdf/";Integrated Security=True;User Instance=True";

                Con = new SqlConnection(str);

          }

          #region SQL语句操作

          // 执行只读数据信息的提取，返回一个datareader

          public SqlDataReader GetReader(string search)

          {

               SqlDataReader Reader;

               if(Con.State != ConnectionState.Open)

                    Con.Open();                           //打开数据库连接

               SqlCommand Com = new SqlCommand(search, Con);

               Reader = Com.ExecuteReader();        //执行SQL语句

               return Reader;                           //返回一个reader

          }

          // 输入查询字符串，返回dataset

          public DataSet getMyDataSet(string sql)

          {

                command.Connection = Con;            //配置command对象

                command.CommandText = sql;           //赋予要执行的语句

                DataSet dt = new DataSet();          //初始化一个数据返回集合

                SqlDataAdapter da = new SqlDataAdapter(command);

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

                da.Fill(dt);                     //执行语句

                command.Connection.Close();          //关闭连接

                return dt;

          }

          // 执行非查询SQL语句

          public void ExecuteSql(string sql)

          {

                if(Con.State != ConnectionState.Open)

                     Con.Open();                      //如果数据连接关闭，则打开

                SqlCommand Com = new SqlCommand(sql, Con);

                Com.ExecuteNonQuery();               //执行非查询SQL语句

                Con.Close();

          }

          // 执行非查询数据库操作，是否关闭数据库连接  可以选择

          public void ExecuteSql(string sql, bool closeConnection)

          {

                if(Con.State != ConnectionState.Open)

                     Con.Open();                      //如果未打开连接，则打开

                SqlCommand Com = new SqlCommand(sql, Con);

                Com.ExecuteNonQuery();

                if (closeConnection) Con.Close();    //如果需要关闭，则关闭连接

          }

          #endregion

          #region 执行存储过程的代码

          //输入存储过程名称，执行查询存储过程

          public DataSet getDataSet(string produreName)

          {

                command.Connection = Con;            //赋予连接对象

               //执行的类型为存储过程

                command.CommandType = CommandType.StoredProcedure  ;

                command.CommandText = produreName;   //赋予执行的存储过程名字

                DataSet dt = new DataSet();

                SqlDataAdapter da = new SqlDataAdapter(command);

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

                da.Fill(dt);                     //填充数据

                command.Connection.Close();

                return dt;                           //返回数据集

          }

          //输入存储过程名，执行非查询存储过程

          public bool exec(string produreName)

          {

                bool flag = false;                   //任务是否正确执行，初始化为false

                command.Connection = Con;            //赋予command对象以数据连接

                command.CommandType = CommandType.StoredProcedure;

                command.CommandText = produreName;   //存储过程名称

                try

                {

                    command.ExecuteNonQuery();        //执行存储过程

                    flag = true;                      //正确完成任务

                }

                finally

                {

                    command.Connection.Close();           //关闭连接

                }

                return flag;                         //返回成功与否的标志

          }

          #endregion

      }

}