数据库封装类的使用

本文由princehao678贡献
doc文档可能在WAP端浏览体验不佳。建议您优先选择TXT，或下载源文件到本机查看。
封装好的数据库类可以方便使用： DataBase类说明： //*********************************************************// //*********************************************************// private void open() 打开数据库 private void close() 关闭数据库 public SqlDataReader GetDataReader(string sqlstring) 输入SQL命令，得到DataReader对象 public DataSet GetDataSet(string sqlstring) 输入SQL命令，得到Dataset public int ExecuteSQL(string sqlstring) 执行非查询命令SQL命令 public int GetdataRow(string sqlstring) 输入SQL命令，检查数据表中是否有该数据信息 public DataTable GetDataTable(string sqlstring) 输入SQL命令，得到DataTable对象 public object GetScalar(string sqlstring) 获取单个值 public bool doUpdate(DataSet ds, String sql, String tableName) 对整体数据集实施批量更新；一般用于 列表这样的对象 public ArrayList GetListArray(string sqlstring,int m) 查询某张表的某列属性的数据，并形成列表 //*********************************************************// //*********************************************************// using using using using using using System; System.Collections; System.Collections.Generic; System.Text; System.Data; System.Data.SqlClient;
namespace WindowsApplication1 { class DataBase { public SqlConnection connection; /// /// 打开数据库 /// private void open() { string connstring = "Data Source=(local);Initial Catalog=school;User ID=sa"; connection = new SqlConnection(connstring); connection.Open(); } /// /// 关闭数据库 /// private void close() { connection.Dispose(); connection.Close(); connection = null; } /// /// 输入SQL命令，得到DataReader对象 /// public SqlDataReader GetDataReader(string sqlstring) { open();
SqlCommand mycom = new SqlCommand(sqlstring, connection); SqlDataReader Dr = mycom.ExecuteReader(); return Dr; } /// /// 输入SQL命令，得到Dataset /// public DataSet GetDataSet(string sqlstring) { open(); SqlCommand mycom = new SqlCommand(sqlstring, connection); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = mycom; DataSet dataset = new DataSet(); adapter.Fill(dataset); close(); return dataset; } /// /// 执行非查询命令SQL命令 /// public int ExecuteSQL(string sqlstring) { int count = -1; open(); try { SqlCommand cmd = new SqlCommand(sqlstring, connection); count = cmd.ExecuteNonQuery(); } catch { count = -1; } finally { close(); } return count; } /// /// 输入SQL命令，检查数据表中是否有该数据信息 /// public int GetdataRow(string sqlstring) { int CountRow = 0; open(); SqlCommand mycom = new SqlCommand(sqlstring, connection); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = mycom; DataSet ds = new DataSet(); da.Fill(ds); ds.CaseSensitive = false;
CountRow = ds.Tables[0].R

ows.Count;//取行集合中的元素的总数 close(); return CountRow; } /// /// 输入SQL命令，得到DataTable对象 /// public DataTable GetDataTable(string sqlstring) { DataSet ds = GetDataSet(sqlstring); DataTable dt = new DataTable(); dt = ds.Tables[0]; return dt; } /// /// 获取单个值 /// public object GetScalar(string sqlstring) { open(); SqlCommand mycom = new SqlCommand(sqlstring, connection); object result = mycom.ExecuteScalar(); close(); return result; } /// /// 对整体数据集实施批量更新；一般用于列表这样的对象 /// ///
DataSet ///
sql语句 ///
表名 /// public bool doUpdate(DataSet ds, String sql, String tableName) { bool flag = false; open(); //强制资源清理;清理非托管资源，不受GC控制的资源。Using结束后会隐式的调用Disposable方 法。 using (SqlDataAdapter da = new SqlDataAdapter(sql, connection)) { //数据库表一定要有主键列 否则此处通不过 SqlCommandBuilder builder = new SqlCommandBuilder(da); try { lock (this) { da.Update(ds, tableName); flag = true; } } catch (SqlException e) { throw new Exception(e.Message);
} } close(); return flag; } /// /// 查询某张表的某列属性的数据，并形成列表 /// ///
查询SQL字串 ///
第m列的属性，整数类型 /// public ArrayList GetListArray(string sqlstring,int m) { ArrayList array = new ArrayList();//创建ArrayList对象 SqlDataReader dr = GetDataReader(sqlstring); while (dr.Read())//遍历所有结果集 { array.Add(dr.GetValue(m));//取到结果集索引的第0列的值并添加到ArrayList对象中 } return array;//返回ArrayList对象 } } }
Mydatabase类操作数据库： using using using using using using System; System.Collections.Generic; System.Collections; System.Text; System.Data; System.Data.SqlClient;
namespace WindowsApplication1 { class Mydatabase { public SqlConnection connection; /// /// 打开数据库 /// public void open() { string connstring = "Data Source=(local);Initial Catalog=Grade_sys;User ID=sa"; connection = new SqlConnection(connstring); connection.Open(); } /// /// 关闭数据库 /// public void close() { connection.Dispose(); connection.Close(); connection = null; } /// /// 输入SQL命令，得到DataReader对象 /// public SqlDataReader GetDataReader(string sqlstring) { open(); SqlCommand mycom = new SqlCommand(sqlstring, connection); SqlDataReader Dr = mycom.ExecuteReader(); return Dr; } /// /// 输入SQL命令，得到Dataset /// public DataSet GetDataSet(string sqlstring) { open(); SqlCommand mycom = new SqlCommand(sqlstring, connection); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = mycom; DataSet dataset = new DataSet(); adapter.Fill(dataset);
close(); return dataset; } /// /// 执行非查询命令SQL命令 /// public int ExecuteSQL(string sqlstring) { int count = -1; open(); try { SqlCommand cmd = new SqlCommand(sqlstring, con

nection); count = cmd.ExecuteNonQuery(); } catch { count = -1; } finally { close(); } return count; } /// /// 输入SQL命令，检查数据表中是否有该数据信息 /// public int GetdataRow(string sqlstring) { int CountRow = 0; open(); SqlCommand mycom = new SqlCommand(sqlstring, connection); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = mycom; DataSet ds = new DataSet(); da.Fill(ds); ds.CaseSensitive = false; CountRow = ds.Tables[0].Rows.Count;//取行集合中的元素的总数 close(); return CountRow; } /// /// 输入SQL命令，得到DataTable对象 /// public DataTable GetDataTable(string sqlstring) { DataSet ds = GetDataSet(sqlstring); DataTable dt = new DataTable(); dt = ds.Tables[0]; return dt; }
/// /// 获取单个值 /// public object GetScalar(string sqlstring) { open(); SqlCommand mycom = new SqlCommand(sqlstring, connection); object result = mycom.ExecuteScalar(); close(); return result; } /// /// 对整体数据集实施批量更新；一般用于列表这样的对象 /// ///
DataSet ///
sql语句 ///
表名 /// public bool doUpdate(DataSet ds, String sql, String tableName) { bool flag = false; open(); //强制资源清理;清理非托管资源，不受GC控制的资源。Using结束后会隐式的调用Disposable方 法。 using (SqlDataAdapter da = new SqlDataAdapter(sql, connection)) { //数据库表一定要有主键列 否则此处通不过 SqlCommandBuilder builder = new SqlCommandBuilder(da); try { lock (this) { da.Update(ds, tableName); flag = true; } } catch (SqlException e) { throw new Exception(e.Message); } } close(); return flag; } /// /// 查询某张表的某列属性的数据，并形成列表 /// ///
查询SQL字串 ///
第m列的属性，整数类型 /// public ArrayList GetListArray(string sqlstring, int m) { ArrayList array = new ArrayList();//创建ArrayList对象 SqlDataReader dr = GetDataReader(sqlstring); while (dr.Read())//遍历所有结果集
{ array.Add(dr.GetValue(m));//取到结果集索引的第0列的值并添加到ArrayList对象中 } return array;//返回ArrayList对象 } /// /// 执行存储过程，返回Command对象 /// public SqlCommand GetProcCommand(string sqlstring) { open(); SqlCommand mycommand = new SqlCommand(sqlstring, connection); return mycommand; } } }
简单的使用说明： 新建一窗体；放置一个 DataGridView 控件：
Mydatabase DB = new Mydatabase(); String sql = ""; sql = "select * from recordinfo"; DataTable dt = DB.GetDataTable(sql); dataGridView1.DataSource = dt; dataGridView1.AllowUserToAddRows = false;