有空再写Oracle、IBM的吧。其实除了连接字符串格式,也只是换换数据库接口名。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace IDataBase
{
public class SQLServer
{
/// <summary>
/// 数据库连接字符串
/// </summary>
public string ConnectionString
{
get;
set;
}
/// <summary>
/// 上一次数据库异常
/// </summary>
public string sqlException
{
get;
set;
}
SqlConnection sqlConn;
SqlCommand sqlCmd;
SqlDataReader reader;
DataSet ds;
SqlCommandBuilder cmdBuilder;
SqlDataAdapter adapter;
/// <summary>
/// 创建一个SQL数据库实例,在设置ConnectionString属性之前无法连接到数据库
/// </summary>
public SQLServer()
{}
/// <summary>
/// 用给定数据库连接字符串创建SQL数据库实例
/// </summary>
/// <param name="connectionString"></param>
public SQLServer(string connectionString)
{
ConnectionString = connectionString;
sqlConn = new SqlConnection(ConnectionString);
}
/// <summary>
/// 用SQL server身份验证方式创建SQL数据库实例,若不使用SQLserver2000应在server后加访问端口“,2317”或“\SQLExpress”
/// </summary>
/// <param name="AttachDBFilename"></param>
/// <param name="server"></param>
/// <param name="DataBase"></param>
/// <param name="uid"></param>
/// <param name="pwd"></param>
public SQLServer(string server ,string DataBase, string uid, string pwd, string AttachDBFilename = null)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
if (AttachDBFilename != null)
{
builder.AttachDBFilename = AttachDBFilename;
}
builder.DataSource = server;
builder.InitialCatalog = DataBase;
builder.UserID = uid;
builder.Password = pwd;
ConnectionString = builder.ConnectionString;
sqlConn = new SqlConnection(ConnectionString);
}
/// <summary>
/// 用SQL server身份验证方式连接默认数据库创建SQL数据库实例,若不使用SQLserver2000应在server后加访问端口“,2317”或“\SQLExpress”
/// </summary>
/// <param name="AttachDBFilename"></param>
/// <param name="server"></param>
/// <param name="uid"></param>
/// <param name="pwd"></param>
public SQLServer(string server, string uid, string pwd, string AttachDBFilename)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.AttachDBFilename = AttachDBFilename;
builder.DataSource = server;
builder.UserInstance = true;
builder.UserID = uid;
builder.Password = pwd;
ConnectionString = builder.ConnectionString;
sqlConn = new SqlConnection(ConnectionString);
}
/// <summary>
/// 用windows身份验证方式创建SQL数据库实例,若不使用SQLserver2000应在server后加访问端口“,2317”或“\SQLExpress”
/// </summary>
/// <param name="server"></param>
/// <param name="DataBase"></param>
/// <param name="AttachDBFilename"></param>
public SQLServer(string server, string DataBase ,string AttachDBFilename = null)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
if( AttachDBFilename != null )
{
builder.AttachDBFilename = AttachDBFilename;
}
builder.DataSource = server;
builder.InitialCatalog = DataBase;
builder.IntegratedSecurity = true;
ConnectionString = builder.ConnectionString;
sqlConn = new SqlConnection(ConnectionString);
}
/// <summary>
/// 用windows身份验证方式连接默认数据库创建SQL数据库实例,若不使用SQLserver2000应在server后加访问端口“,2317”或“\SQLExpress”
/// </summary>
/// <param name="server"></param>
/// <param name="AttachDBFilename"></param>
public SQLServer(string server, string AttachDBFilename)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.AttachDBFilename = AttachDBFilename;
builder.DataSource = server;
builder.UserInstance = true;
builder.IntegratedSecurity = true;
ConnectionString = builder.ConnectionString;
sqlConn = new SqlConnection(ConnectionString);
}
/// <summary>
/// 使用SQL连接字符串编辑器完成连接字符串的微调,并用创建的连接字符串创建数据库实例
/// </summary>
/// <param name="sqlConnStrBuilder"></param>
public SQLServer(SqlConnectionStringBuilder sqlConnStrBuilder)
{
ConnectionString = sqlConnStrBuilder.ConnectionString;
sqlConn = new SqlConnection(ConnectionString);
}
/// <summary>
/// 打开SQL数据库连接
/// </summary>
/// <returns></returns>
private bool openConnection()
{
try
{
sqlConn.Open();
return true;
}
catch (Exception e)
{
sqlException = e.ToString();
return false;
}
}
/// <summary>
/// 关闭SQL数据库连接
/// </summary>
private void closeConnection()
{
sqlConn.Close();
}
/// <summary>
/// 执行SQL命令
/// </summary>
/// <param name="cmd"></param>
private bool excuteCmd()
{
try
{
openConnection();
sqlCmd.ExecuteNonQuery();
closeConnection();
return true;
}
catch (Exception e)
{
sqlException = e.ToString();
closeConnection();
return false;
}
}
/// <summary>
/// 执行sql语句,成功返回true,失败返回false
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public bool runSqlCmd(string cmd,sqlConn)
{
sqlCmd = new SqlCommand(cmd);
return excuteCmd();
}
/// <summary>
/// 由用户自行生成一条sql命令并执行
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public bool runSqlCmd(SqlCommand cmd)
{
sqlCmd = cmd;
return excuteCmd();
}
/// <summary>
/// 按sql查询语句获得目标数据表的数据,返回DataTable。
/// </summary>
/// <param name="sqlQuery"></param>
/// <returns></returns>
public DataTable getDataTableBySQL(string sqlQuery)
{
ds = new DataSet();
adapter = new SqlDataAdapter(sqlQuery, sqlConn);
cmdBuilder = new SqlCommandBuilder(adapter);
openConnection();
adapter.Fill(ds);
closeConnection();
return ds.Tables[0];
}
/// <summary>
/// 获得目标数据表的数据,返回DataTable。
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataTable getDataTableByName(string tableName)
{
ds = new DataSet();
adapter = new SqlDataAdapter("select * from "+tableName,sqlConn);
cmdBuilder = new SqlCommandBuilder(adapter);
openConnection();
adapter.Fill(ds,tableName);
closeConnection();
return ds.Tables[tableName];
}
/// <summary>
/// 将数据库中指定的一或多个数据表填入DataSet并返回。
/// </summary>
/// <param name="sqlQuery"></param>
/// <returns></returns>
public DataSet getDataSet(string[] tableNameArray)
{
ds = new DataSet();
openConnection();
foreach (string i in tableNameArray)
{
adapter = new SqlDataAdapter("select * from "+i, sqlConn);
cmdBuilder = new SqlCommandBuilder(adapter);
adapter.Fill(ds,i);
}
closeConnection();
return ds;
}
/// <summary>
/// 执行带1个参数的sql插入语句。sql语句中的参数名,与对应parameterName的值都请用“@para”的格式。成功返回true,失败返回false。
/// </summary>
/// <param name="sql"></param>
/// <param name="parameterName"></param>
/// <param name="dbType"></param>
/// <param name="value"></param>
/// <returns></returns>
public bool insert(string sql, string parameterName, SqlDbType dbType , int size, object value)
{
try
{
openConnection();
sqlCmd = new SqlCommand();
sqlCmd.Parameters.Add(parameterName, dbType, size);
sqlCmd.Parameters[parameterName].Value = value;
sqlCmd.ExecuteNonQuery();
closeConnection();
return true;
}
catch (Exception e)
{
sqlException = e.ToString();
closeConnection();
return false;
}
}
/// <summary>
/// 生成一个指定数据表的空数据列
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataRow creatDataRow(string tableName)
{
ds = new DataSet();
adapter = new SqlDataAdapter("select * from "+tableName, sqlConn);
cmdBuilder = new SqlCommandBuilder(adapter);
openConnection();
adapter.Fill(ds, tableName);
closeConnection();
return ds.Tables[tableName].NewRow();
}
/// <summary>
/// 向指定数据表插入数据列,空数据列可以用当前类的creatDataRow方法获得。成功返回true,失败返回false。
/// </summary>
/// <param name="tableName"></param>
/// <param name="row"></param>
/// <returns></returns>
public bool insert(string tableName, DataRow row)
{
try
{
ds = new DataSet();
adapter = new SqlDataAdapter("select * from "+tableName, sqlConn);
cmdBuilder = new SqlCommandBuilder(adapter);
openConnection();
adapter.Fill(ds, tableName);
ds.Tables[tableName].Rows.Add(row.ItemArray);
adapter.Update(ds, tableName);
closeConnection();
return true;
}
catch (Exception e)
{
sqlException = e.ToString();
closeConnection();
return false;
}
}
/// <summary>
/// 执行带1个参数的sql更新语句。sql语句中的参数名,与对应parameterName的值都请用“@para”的格式。成功返回true,失败或没找到指定行返回false。
/// </summary>
/// <param name="sql"></param>
/// <param name="parameterName"></param>
/// <param name="dbType"></param>
/// <param name="size"></param>
/// <param name="value"></param>
/// <returns></returns>
public bool update(string sql, string parameterName, SqlDbType dbType, int size, object value)
{
try
{
openConnection();
sqlCmd = new SqlCommand();
sqlCmd.Parameters.Add(parameterName, dbType, size);
sqlCmd.Parameters[parameterName].Value = value;
if (sqlCmd.ExecuteNonQuery() == 0)
{
closeConnection();
return false;
}
closeConnection();
return true;
}
catch (Exception e)
{
sqlException = e.ToString();
closeConnection();
return false;
}
}
/// <summary>
/// 用当前类的getDataSet方法获取指定数据表,修改后用本方法批量地更新指定数据表。成功返回true,失败返回false。
/// </summary>
/// <param name="tableName"></param>
/// <param name="dataSet"></param>
/// <returns></returns>
public bool update(string tableName, DataSet dataSet)
{
try
{
adapter = new SqlDataAdapter("select * from "+tableName, sqlConn);
cmdBuilder = new SqlCommandBuilder(adapter);
openConnection();
adapter.Update(dataSet);
closeConnection();
return true;
}
catch (Exception e)
{
sqlException = e.ToString();
closeConnection();
return false;
}
}
/// <summary>
/// 返回指定数据表的指定列中是否存在指定值
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <returns></returns>
public bool existInTable(string tableName, string columnName, string value)
{
sqlCmd = new SqlCommand("select * from "+tableName+" where "+columnName+"='"+value+"'",sqlConn);
openConnection();
reader = sqlCmd.ExecuteReader(CommandBehavior.SingleResult);
bool exist = reader.HasRows;
reader.Close();
closeConnection();
return exist;
}
/// <summary>
/// 返回指定数据表的指定列中有多少个指定值
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <returns></returns>
public int countInTable(string tableName, string columnName, string value)
{
sqlCmd = new SqlCommand("select count(*) from " + tableName + " where " + columnName + "='" + value + "'", sqlConn);
openConnection();
int count = (int)sqlCmd.ExecuteScalar();
closeConnection();
return count;
}
/// <summary>
/// DataReader使用完毕必须用当前类的close方法将DataReader关闭,并关闭数据库连接
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public SqlDataReader select(string sql)
{
sqlCmd = new SqlCommand(sql, sqlConn);
openConnection();
reader = sqlCmd.ExecuteReader();
return reader;
}
/// <summary>
/// 如果本类提供的DataReader未关闭,将其关闭,同时关闭未关闭的数据库连接
/// </summary>
public void close()
{
if (!reader.IsClosed)
{
reader.Close();
}
sqlConn.Close();
}
}
public class Access
{
/// <summary>
/// 数据库连接字符串
/// </summary>
public string ConnectionString
{
get;
set;
}
/// <summary>
/// 上一次数据库异常
/// </summary>
public string oleException
{
get;
set;
}
OleDbConnection conn;
OleDbCommand cmd;
OleDbDataReader reader;
DataSet ds;
OleDbCommandBuilder cmdBuilder;
OleDbDataAdapter adapter;
/// <summary>
/// 创建一个OleDb数据库实例,在设置ConnectionString属性之前无法连接到数据库
/// </summary>
public Access()
{ }
/// <summary>
/// 用给定数据库连接字符串创建OleDb数据库实例
/// </summary>
/// <param name="connectionString"></param>
public Access(string connectionString)
{
ConnectionString = connectionString;
conn = new OleDbConnection(ConnectionString);
}
/// <summary>
/// 用数据源提供程序和数据库文件名进行简易连接,数据库文件在项目默认数据库路径下
/// </summary>
/// <param name="provider"></param>
/// <param name="dataBase"></param>
public Access(string provider, string dataBaseName)
{
OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
builder.Provider = provider;
builder.DataSource = "|DataDirectory|"+dataBaseName;
ConnectionString = builder.ConnectionString;
conn = new OleDbConnection(ConnectionString);
}
/// <summary>
/// 使用OleDb连接字符串编辑器完成连接字符串的微调,并用创建的连接字符串创建数据库实例
/// </summary>
/// <param name="sqlConnStrBuilder"></param>
public Access(OleDbConnectionStringBuilder oleConnStrBuilder)
{
ConnectionString = oleConnStrBuilder.ConnectionString;
conn = new OleDbConnection(ConnectionString);
}
/// <summary>
/// 打开OleDb数据库连接
/// </summary>
/// <returns></returns>
private bool openConnection()
{
try
{
conn.Open();
return true;
}
catch (Exception e)
{
oleException = e.ToString();
return false;
}
}
/// <summary>
/// 关闭OleDb数据库连接
/// </summary>
private void closeConnection()
{
conn.Close();
}
/// <summary>
/// 执行SQL命令
/// </summary>
/// <param name="cmd"></param>
private bool excuteCmd()
{
try
{
openConnection();
cmd.ExecuteNonQuery();
closeConnection();
return true;
}
catch (Exception e)
{
oleException = e.ToString();
closeConnection();
return false;
}
}
/// <summary>
/// 执行sql语句,成功返回true,失败返回false
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public bool runSqlCmd(string _cmd)
{
cmd = new OleDbCommand(_cmd,conn);
return excuteCmd();
}
/// <summary>
/// 由用户自行生成一条sql命令并执行
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public bool runSqlCmd(OleDbCommand _cmd)
{
cmd = _cmd;
return excuteCmd();
}
/// <summary>
/// 按sql查询语句获得目标数据表的数据,返回DataTable。
/// </summary>
/// <param name="sqlQuery"></param>
/// <returns></returns>
public DataTable getDataTableBySQL(string sqlQuery)
{
ds = new DataSet();
adapter = new OleDbDataAdapter(sqlQuery, conn);
cmdBuilder = new OleDbCommandBuilder(adapter);
openConnection();
adapter.Fill(ds);
closeConnection();
return ds.Tables[0];
}
/// <summary>
/// 获得目标数据表的数据,返回DataTable。
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataTable getDataTableByName(string tableName)
{
ds = new DataSet();
adapter = new OleDbDataAdapter("select * from " + tableName, conn);
cmdBuilder = new OleDbCommandBuilder(adapter);
openConnection();
adapter.Fill(ds, tableName);
closeConnection();
return ds.Tables[tableName];
}
/// <summary>
/// 将数据库中指定的一或多个数据表填入DataSet并返回。
/// </summary>
/// <param name="sqlQuery"></param>
/// <returns></returns>
public DataSet getDataSet(string[] tableNameArray)
{
ds = new DataSet();
openConnection();
foreach (string i in tableNameArray)
{
adapter = new OleDbDataAdapter("select * from " + i, conn);
cmdBuilder = new OleDbCommandBuilder(adapter);
adapter.Fill(ds, i);
}
closeConnection();
return ds;
}
/// <summary>
/// 执行带1个参数的sql插入语句。sql语句中的参数名,与对应parameterName的值都请用“@para”的格式。成功返回true,失败返回false。
/// </summary>
/// <param name="sql"></param>
/// <param name="parameterName"></param>
/// <param name="dbType"></param>
/// <param name="value"></param>
/// <returns></returns>
public bool insert(string sql, string parameterName, OleDbType dbType, int size, object value)
{
try
{
openConnection();
cmd = new OleDbCommand();
cmd.Parameters.Add(parameterName, dbType, size);
cmd.Parameters[parameterName].Value = value;
cmd.ExecuteNonQuery();
closeConnection();
return true;
}
catch (Exception e)
{
oleException = e.ToString();
closeConnection();
return false;
}
}
/// <summary>
/// 生成一个指定数据表的空数据列
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public DataRow creatDataRow(string tableName)
{
ds = new DataSet();
adapter = new OleDbDataAdapter("select * from " + tableName, conn);
cmdBuilder = new OleDbCommandBuilder(adapter);
openConnection();
adapter.Fill(ds, tableName);
closeConnection();
return ds.Tables[tableName].NewRow();
}
/// <summary>
/// 向指定数据表插入数据列,空数据列可以用当前类的creatDataRow方法获得。成功返回true,失败返回false。
/// </summary>
/// <param name="tableName"></param>
/// <param name="row"></param>
/// <returns></returns>
public bool insert(string tableName, DataRow row)
{
try
{
ds = new DataSet();
adapter = new OleDbDataAdapter("select * from " + tableName, conn);
cmdBuilder = new OleDbCommandBuilder(adapter);
openConnection();
adapter.Fill(ds, tableName);
ds.Tables[tableName].Rows.Add(row.ItemArray);
adapter.Update(ds, tableName);
closeConnection();
return true;
}
catch (Exception e)
{
oleException = e.ToString();
closeConnection();
return false;
}
}
/// <summary>
/// 执行带1个参数的sql更新语句。sql语句中的参数名,与对应parameterName的值都请用“@para”的格式。成功返回true,失败或没找到指定行返回false。
/// </summary>
/// <param name="sql"></param>
/// <param name="parameterName"></param>
/// <param name="dbType"></param>
/// <param name="size"></param>
/// <param name="value"></param>
/// <returns></returns>
public bool update(string sql, string parameterName, OleDbType dbType, int size, object value)
{
try
{
openConnection();
cmd = new OleDbCommand();
cmd.Parameters.Add(parameterName, dbType, size);
cmd.Parameters[parameterName].Value = value;
if (cmd.ExecuteNonQuery() == 0)
{
closeConnection();
return false;
}
closeConnection();
return true;
}
catch (Exception e)
{
oleException = e.ToString();
closeConnection();
return false;
}
}
/// <summary>
/// 用当前类的getDataSet方法获取指定数据表,修改后用本方法批量地更新指定数据表。成功返回true,失败返回false。
/// </summary>
/// <param name="tableName"></param>
/// <param name="dataSet"></param>
/// <returns></returns>
public bool update(string tableName, DataSet dataSet)
{
try
{
adapter = new OleDbDataAdapter("select * from " + tableName, conn);
cmdBuilder = new OleDbCommandBuilder(adapter);
openConnection();
adapter.Update(dataSet);
closeConnection();
return true;
}
catch (Exception e)
{
oleException = e.ToString();
closeConnection();
return false;
}
}
/// <summary>
/// 返回指定数据表的指定列中是否存在指定值
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <returns></returns>
public bool existInTable(string tableName, string columnName, string value)
{
cmd = new OleDbCommand("select * from " + tableName + " where " + columnName + "='" + value + "'", conn);
openConnection();
reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
bool exist = reader.HasRows;
reader.Close();
closeConnection();
return exist;
}
/// <summary>
/// 返回指定数据表的指定列中有多少个指定值
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <returns></returns>
public int countInTable(string tableName, string columnName, string value)
{
cmd = new OleDbCommand("select count(*) from " + tableName + " where " + columnName + "='" + value + "'", conn);
openConnection();
int count = (int)cmd.ExecuteScalar();
closeConnection();
return count;
}
/// <summary>
/// DataReader使用完毕必须用当前类的close方法将DataReader关闭,并关闭数据库连接
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public OleDbDataReader select(string sql)
{
cmd = new OleDbCommand(sql, conn);
openConnection();
reader = cmd.ExecuteReader();
return reader;
}
/// <summary>
/// 如果本类提供的DataReader未关闭,将其关闭,同时关闭未关闭的数据库连接
/// </summary>
public void close()
{
if (!reader.IsClosed)
{
reader.Close();
}
conn.Close();
}
}
}