首先，我们建立一个数据库访问类，该类具有普遍性，代码如下：
//===============建立数据库类文件DataBase.cs====================
using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace WindowsApplication1
{
class Mydatabase
{
public SqlConnection connection;
/// <summary>
/// 打开数据库
/// </summary>
public void open()
{
string connstring = "Data Source=LKJ\\SQLEXPRESS;Initial Catalog=Grade_sys;Integrated Security=True";
connection = new SqlConnection(connstring);
connection.Open();
}
/// <summary>
/// 关闭数据库
/// </summary>
public void close()
{
connection.Dispose();
connection.Close();
connection = null;
}
/// <summary>
/// 输入SQL命令，得到DataReader对象
/// </summary>
public SqlDataReader GetDataReader(string sqlstring)
{
open();
SqlCommand mycom = new SqlCommand(sqlstring, connection);
SqlDataReader Dr = mycom.ExecuteReader();
return Dr;
}
/// <summary>
/// 输入SQL命令，得到Dataset
/// </summary>
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;
}
/// <summary>
/// 执行非查询命令SQL命令
/// </summary>
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;
}
/// <summary>
/// 输入SQL命令，检查数据表中是否有该数据信息
/// </summary>
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;
}
/// <summary>
/// 输入SQL命令，得到DataTable对象
/// </summary>
public DataTable GetDataTable(string sqlstring)
{
DataSet ds = GetDataSet(sqlstring);
DataTable dt = new DataTable();
dt = ds.Tables[0];
return dt;
}
/// <summary>
/// 获取单个值
/// </summary>
public object GetScalar(string sqlstring)
{
open();
SqlCommand mycom = new SqlCommand(sqlstring, connection);
object result = mycom.ExecuteScalar();
close();
return result;
}
/// <summary>
/// 对整体数据集实施批量更新；一般用于列表这样的对象
/// </summary>
/// <param name="ds">DataSet</param>
/// <param name="sql">sql语句</param>
/// <param name="tableName">表名</param>
/// <returns>bool类型数据，表示是否修改成功</returns>
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;
}
/// <summary>
/// 查询某张表的某列属性的数据，并形成列表
/// </summary>
/// <param name="sqlstring">查询SQL字串</param>
/// <param name="m">第m列的属性，整数类型</param>
/// <returns> ArrayList类型数据，存储在ArrayList中的一组数据</returns>
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对象
}
/// <summary>
/// 执行存储过程，返回Command对象
/// </summary>
public SqlCommand GetProcCommand(string sqlstring)
{
open();
SqlCommand mycommand = new SqlCommand(sqlstring, connection);
return mycommand;
}
}
}
本文转自 qianshao 51CTO博客，原文链接：http://blog.51cto.com/qianshao/216102，如需转载请自行联系原作者