Question

c#中连接数据库的类怎么写呀?

朋友，帮帮忙。是类，我实在是写不出来。写在form里会，可类就不知道了。

Answer 1

我有一个,你试试
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

/// <summary>
///SqlConnDb类,适用于Sql数据库操作
/// </summary>
public class SqlConnDb
{
SqlConnection conn = null; //连接数据库的对象

/// <summary>
/// 构造函数,连接数据库，数据库连接字符在web.Config文件的AppSettings下的conStr
/// </summary>
public SqlConnDb()
{
if (conn == null)//判断连接是否为空
{
//string conString = "provider=sqloledb.1;data source=.;initial catalog=capucivar;user id=sa;pwd=";//连接数据库的字符串
string conString = System.Configuration.ConfigurationManager.AppSettings["conStr"];//连接数据库的字符串
conn = new SqlConnection(conString);

if (conn.State == ConnectionState.Closed)
{
conn.Open();//打开数据库连接

}
}
}

/// <summary>
/// 从数据库中查询数据的,返回为DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet query(string sql)
{
DataSet ds = new DataSet();//DataSet是表的集合

SqlDataAdapter da = new SqlDataAdapter(sql, conn);//从数据库中查询

da.Fill(ds);//将数据填充到DataSet

connClose();//关闭连接

return ds;//返回结果

}

/// <summary>
/// 更新数据库
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int update(string sql)
{
SqlCommand oc = new SqlCommand();//表示要对数据源执行的SQL语句或存储过程

oc.CommandText = sql;//设置命令的文本

oc.CommandType = CommandType.Text;//设置命令的类型

oc.Connection = conn;//设置命令的连接

int x = oc.ExecuteNonQuery();//执行SQL语句

connClose();//关闭连接

return x; //返回一个影响行数

}

/// <summary>
/// 关闭数据库连接
/// </summary>
public void connClose()
{
if (conn.State == ConnectionState.Open)
{//判断数据库的连接状态，如果状态是打开的话就将它关闭

conn.Close();
}
}

}

/// <summary>
///OleDbConnDb类,适用于Accsee数据库操作
/// </summary>
public class OleDbConnDb
{
OleDbConnection conn = null; //连接数据库的对象

/// <summary>
/// 构造函数,连接数据库，数据库连接字符在web.Config文件的AppSettings下的conStr
/// </summary>
public OleDbConnDb()
{
if (conn == null)//判断连接是否为空
{
//string conString = "provider=sqloledb.1;data source=.;initial catalog=capucivar;user id=sa;pwd=";//连接数据库的字符串
string conString = System.Configuration.ConfigurationManager.AppSettings["conStr"];//连接数据库的字符串
conn = new OleDbConnection(conString);

if (conn.State == ConnectionState.Closed)
{
conn.Open();//打开数据库连接

}
}
}

/// <summary>
/// 从数据库中查询数据的,返回为DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet query(string sql)
{
DataSet ds = new DataSet();//DataSet是表的集合

OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);//从数据库中查询

da.Fill(ds);//将数据填充到DataSet

connClose();//关闭连接

return ds;//返回结果

}

/// <summary>
/// 更新数据库
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int update(string sql)
{
OleDbCommand oc = new OleDbCommand();//表示要对数据源执行的SQL语句或存储过程

oc.CommandText = sql;//设置命令的文本

oc.CommandType = CommandType.Text;//设置命令的类型

oc.Connection = conn;//设置命令的连接

int x = oc.ExecuteNonQuery();//执行SQL语句

connClose();//关闭连接

return x; //返回一个影响行数

}

/// <summary>
/// 关闭数据库连接
/// </summary>
public void connClose()
{
if (conn.State == ConnectionState.Open)
{//判断数据库的连接状态，如果状态是打开的话就将它关闭

conn.Close();
}
}

}

Answer 2

可以手写，需要重写几个方法。
可以用生成器生成数据库连接类，相当规范（有包括Microsoft官方出的连接类）
也可以用已经生成的程序集(.dll)，你只需要调用就行。

Answer 3

楼一给的个只能连接sql,楼二是忽悠,我的可以连接access,sql,oracle,excel ,纯粹是自用的,
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace CheckMaterial
{
static class SqlHelp
{
/// <summary>
/// link access
/// </summary>
public static string connstring=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=cost.mdb;Jet OLEDB:Database Password=1234567";

/// <summary>
/// link sql
/// </summary>
////public static string connstring = @"Provider=SQLOLEDB; data source=server1;user id=sa;password=;initial catalog=ytdb"
/// <summary>
/// link oracle
/// </summary>
//public static string connstring = @"Provider=OraOLEDB.Oracle;Persist Security Info=false;User ID=;Password=;Data Source=";
//public static string connstring = @"Provider=msdaora.1;Persist Security Info=false;User ID=;Password=;Data Source=";

/// <summary>
/// link excel
/// </summary>
//public static string connstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"

/// <summary>
/// SqlExecuteNonQuery
/// </summary>
/// <param name="sql"></param>
/// <returns>using try catch to catch the error msg</returns>
public static int SqlExecuteNonQuery(string sql)
{
//OleDbConnection conn = new OleDbConnection(connstring);
using (OleDbConnection conn = new OleDbConnection(connstring))
{
try
{
conn.Close();
conn.Open();
OleDbCommand cmd = new OleDbCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
catch (Exception e)
{
conn.Close();
//System.Windows.Forms.MessageBox.Show(eee.Message);
throw new Exception(e.Message);
}
}
}

/// <summary>
/// SqlExecuteNonQuery
/// </summary>
/// <param name="sql"></param>
/// <param name="_connstring"></param>
/// <returns>using try catch to catch the error msg</returns>
public static int SqlExecuteNonQuery(string sql, string _connstring)
{
//OleDbConnection conn = new OleDbConnection(connstring);
using (OleDbConnection conn = new OleDbConnection(_connstring))
{
try
{
conn.Close();
conn.Open();
OleDbCommand cmd = new OleDbCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
catch (Exception e)
{
conn.Close();
//System.Windows.Forms.MessageBox.Show(eee.Message);
throw new Exception(e.Message);
}
}
}

/// <summary>
/// SqlExcuteScalar
/// </summary>
/// <param name="sql"></param>
/// <returns>using try catch to catch the error msg</returns>
public static object SqlExcuteScalar(string sql)
{
using (OleDbConnection conn = new OleDbConnection(connstring))
{
try
{
conn.Close();
conn.Open();
OleDbCommand cmd = new OleDbCommand(sql, conn);
object obj = cmd.ExecuteScalar();
conn.Close();
return obj;
}
catch (Exception e)
{
conn.Close();
throw new Exception(e.Message);
}
}
}

/// <summary>
/// SqlExcuteScalar
/// </summary>
/// <param name="sql"></param>
/// <param name="_connstring"></param>
/// <returns>using try catch to catch the error msg</returns>
public static object SqlExcuteScalar(string sql, string _connstring)
{
using (OleDbConnection conn = new OleDbConnection(_connstring))
{
try
{
conn.Close();
conn.Open();
OleDbCommand cmd = new OleDbCommand(sql, conn);
object obj = cmd.ExecuteScalar();
conn.Close();
return obj;
}
catch (Exception e)
{
conn.Close();
throw new Exception(e.Message);
}
}
}

/// <summary>
/// getDataTableFormSql
/// </summary>
/// <param name="sql"></param>
/// <returns>using try catch to catch the error msg</returns>
public static DataTable getDataTableFormSql(string sql)
{
DataTable dt = new DataTable();
using (OleDbConnection conn = new OleDbConnection(connstring))
{
try
{
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
oda.Fill(dt);
cmd.Dispose();
return dt;
}
catch (Exception e)
{
conn.Close();
throw new Exception(e.Message);
}
}
}

/// <summary>
/// getDataTableFormSql
/// </summary>
/// <param name="sql"></param>
/// <param name="_connstring"></param>
/// <returns>using try catch to catch the error msg</returns>
public static DataTable getDataTableFormSql(string sql, string _connstring)
{
DataTable dt = new DataTable();
using (OleDbConnection conn = new OleDbConnection(_connstring))
{
try
{
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
oda.Fill(dt);
cmd.Dispose();
return dt;
}
catch (Exception e)
{
conn.Close();
throw new Exception(e.Message);
}
}
}

/// <summary>
/// SQLExecuteTransaction
/// </summary>
/// <param name="sqls"></param>
/// <returns>using try catch to catch the error msg</returns>
public static bool SQLExecuteTransaction(string[] sqls)
{
using (OleDbConnection conn = new OleDbConnection(connstring))
{
try
{
conn.Open();
OleDbTransaction trans = conn.BeginTransaction();
try
{

OleDbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
//cmd.CommandText
foreach (string s in sqls)
{
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
trans.Commit();
return true;
}
catch (Exception e)
{
trans.Rollback();
throw new Exception(e.Message);
}
finally
{
conn.Close();
}
}
catch (Exception e)
{
//trans.Rollback();
throw new Exception(e.Message);
}
}
}

/// <summary>
/// SQLExecuteTransaction
/// </summary>
/// <param name="sqls"></param>
/// <param name="_connstring"></param>
/// <returns>using try catch to catch the error msg</returns>
public static bool SQLExecuteTransaction(string[] sqls, string _connstring)
{
using (OleDbConnection conn = new OleDbConnection(_connstring))
{
try
{
conn.Open();
OleDbTransaction trans = conn.BeginTransaction();
try
{

OleDbCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
//cmd.CommandText
foreach (string s in sqls)
{
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
trans.Commit();
return true;
}
catch (Exception e)
{
trans.Rollback();
throw new Exception(e.Message);
}
finally
{
conn.Close();
}
}
catch (Exception e)
{
//trans.Rollback();
throw new Exception(e.Message);
}
}
}

}
}

Answer 4

可以选择封装到类库里，这个你可以参考微软PETSHOP设计里的，SQLHELP类。