using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;namespace CRM
{
class clsDatabases
{
private string strError = null;
private int intCount = 0;
private int intId = 0;
private SqlTransaction trans = null;
public clsDatabases()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 公开方法getConnectionString，返回数据库连接字符串
/// </summary>
/// <returns></returns>
public string getConnectionString()
{
connectionStr = "Server=(local);Database=crm;Uid=crm;pwd=crm;";
return connectionStr;
}
/// <summary>
/// 公开方法sqlConnection，返回数据库连接
/// </summary>
/// <returns></returns>
public SqlConnection sqlConnection()
{
try
{
return new SqlConnection(getConnectionString());
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// 公开属性errorMessage，返回错误信息
/// </summary>
public string errorMessage
{
get
{
return strError;
}
}
/// <summary>
/// 根据查询语句从数据库检索数据
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="sqlConn">数据库连接</param>
/// <returns>有数据则返回DataSet对象，否则返回null</returns>
public DataTable selectTable(string sql, SqlConnection sqlConn)
{
strError = "";
SqlConnection conn;
if (sqlConn == null)
{
conn = sqlConnection();
}
else
{
conn = sqlConn;
}
try
{
//若数据库连接的当前状态是关闭的，则打开连接
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(sql, conn);
DataTable dt = new DataTable();
mySqlDataAdapter.Fill(dt);
return dt;
}
catch (Exception e)
{
strError = "数据检索失败：" + e.Message;
return null;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
/// <summary>
/// 获取最新插入ID
/// </summary>
/// <returns>最新插入ID</returns>
public int getInsertId()
{
return intId;
}
/// <summary>
/// 把数据插入数据库
/// </summary>
/// <param name="sql">Insert Sql语句</param>
/// <param name="sqlConn">数据库连接</param>
/// <returns>插入成功返回true</returns>
public bool insert(string sql, SqlConnection sqlConn)
{
strError = "";
SqlConnection conn;
if (sqlConn == null)
{
conn = sqlConnection();
}
else
{
conn = sqlConn;
}
try
{
//若数据库连接的当前状态是关闭的，则打开连接
if (conn.State == ConnectionState.Closed)
{
conn.Open();
} //查录插入记录的ID
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(sql+"SELECT @@IDENTITY;", conn);
DataTable dt = new DataTable();
mySqlDataAdapter.Fill(dt); if (dt.Rows.Count != 0)
{
intId = Convert.ToInt32(dt.Rows[0][0]);
return true;
}
else
{
return false;
}
}
catch (Exception e)
{
strError = "插入数据库失败：" + e.Message;
return false;
}
finally
{
}
}
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="sql">update Sql语句</param>
/// <param name="sqlConn">数据库连接</param>
/// <returns>更新成功返回true</returns>
public bool update(string sql, SqlConnection sqlConn)
{
return executeSQL(sql, sqlConn);
}
/// <summary>
/// 从数据库中删除数据
/// </summary>
/// <param name="sql">delete Sql语句</param>
/// <param name="sqlConn">数据库连接</param>
/// <returns>删除成功返回true</returns>
public bool delete(string sql, SqlConnection sqlConn)
{
return executeSQL(sql, sqlConn);
}
/// <summary>
/// 根据Sql语句更新数据库
/// </summary>
/// <param name="sql">更新语句</param>
/// <param name="sqlConn">数据库连接</param>
/// <returns>更新成功则返回true</returns>
public bool executeSQL(string sql, SqlConnection sqlConn)
{
strError = "";
SqlConnection conn;
if (sqlConn == null)
{
conn = sqlConnection();
}
else
{
conn = sqlConn;
}
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand(sql, conn);
if (trans != null)
{
cmd.Transaction = trans;//事务添加
}
cmd.CommandType = CommandType.Text;
intCount = cmd.ExecuteNonQuery();
return true;
}
catch (Exception e)
{
strError = "更新数据库失败：" + e.Message;
return false;
}
finally
{
}
}
/// <summary>
/// 开启事务
/// </summary>
/// <param name=""></param>
/// <returns>无返回</returns>
public bool transBegin(SqlConnection sqlConn)
{
SqlConnection conn;
if (sqlConn == null)
{
conn = sqlConnection();
}
else
{
conn = sqlConn;
}
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
trans = conn.BeginTransaction();
return true;
}
/// <summary>
/// 结束事务
/// </summary>
/// <param name=""></param>
/// <returns>无返回</returns>
public bool transCommit()
{
trans.Commit();
trans.Dispose();
return true;
}
/// <summary>
/// 回滚事务
/// </summary>
/// <param name=""></param>
/// <returns>无返回</returns>
public bool transRollback()
{
trans.Rollback();
trans.Dispose();
return true;
}
}
}
上面是我写的C#数据库操作类,和大家探讨如果精减,如何写的更完善,如执行基本操作/事务/过程等等
下载地址:http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp(原版)
就算是造福后来者吧
public static SqlConnection connection;
public static SqlConnection Connection
{
get
{
if (connection == null)
{
//string connectionString = ConfigurationManager.ConnectionStrings["******"].ConnectionString;
string strConn = @"Data Source=(local);Initial Catalog=******;Integrated Security=True";
connection = new SqlConnection(strConn);
connection.Open();
}
else if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
/// <summary>
/// 执行增,删,改,的方法
/// </summary>
/// <param name="commandText">sql,proc</param>
/// <param name="commandType">CommandType</param>
/// <param name="para"></param>
/// <returns>int</returns>
public static int ExecuteCommand(string commandText, CommandType commandType,SqlParameter[] para)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType;
try
{
if (para != null)
{
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteNonQuery();
} finally
{
connection.Close();
}
}事物如果用三层的话，放在BLL