C#数据库类操作

网集三种经典操纵

******************************

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace EduNet.UserClass
{
/// <summary>
/// LinkAccess Access数据库操作类
/// </summary>
public class LinkAccess
{
protected static OleDbConnection Connection;
private static string connectionString;
public LinkAccess()
{
//
// TODO: 在此处添加构造函数逻辑
//
}

/// <summary>
/// 获取Access数据库连接的方法
/// </summary>
/// <returns></returns>
private static void ConnInit()
{
string connStr;
string accessPath=System.Web.HttpContext.Current.Server.MapPath(ConfigurationSettings.AppSettings["dbPath"].ToString().Trim());
connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+accessPath;
connectionString=connStr;
Connection=new OleDbConnection(connectionString);
}


/// <summary>
/// 执行SQL语句，返回数据到DataSet中
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
///
public static DataSet ReturnDataSet(string sql,string table)
{
DataSet dataSet=new DataSet();
ConnInit();
Connection.Open();
OleDbDataAdapter sqlDA=new OleDbDataAdapter(sql,Connection);
sqlDA.Fill(dataSet,table);
Connection.Close();

return dataSet;
}

/// <summary>
/// 执行SQL语句，返回 DataReader
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
///
public static OleDbDataReader ReturnDataReader(String sql)
{
ConnInit();
Connection.Open();
OleDbCommand command=new OleDbCommand(sql,Connection);
OleDbDataReader dataReader=command.ExecuteReader();
//Connection.Close();
return dataReader;
}

/// <summary>
/// 执行SQL语句，返回记录数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
///
public static int ReturnRecordCount(string sql)
{
int recordCount=0;

ConnInit();
Connection.Open();
OleDbCommand command=new OleDbCommand(sql,Connection);
OleDbDataReader dataReader=command.ExecuteReader();

while(dataReader.Read())
{
recordCount++;
}
dataReader.Close();
Connection.Close();

return recordCount;
}

/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static bool EditDatabase(string sql)
{
bool successState=false;

ConnInit();
Connection.Open();
OleDbTransaction myTrans=Connection.BeginTransaction();
OleDbCommand command=new OleDbCommand(sql,Connection,myTrans);

try
{
command.ExecuteNonQuery();
myTrans.Commit();
successState=true;
}
catch
{
myTrans.Rollback();
}
finally
{
command.Dispose();
Connection.Close();
}
return successState;
}

/// <summary>
/// 返回一个整形的结果,第一行第一列的值
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int ExecuteSqlValueOfInt(string strSQL)
{
ConnInit();//打开数据库
Connection.Open();
OleDbCommand mycmd=new OleDbCommand(strSQL,Connection);
try
{
object r=mycmd.ExecuteScalar();
if(Object.Equals(r,null))
{
throw new Exception("value nuavilable");
}
else
{
return (int)r;
}
}
catch
{
return 0;
}
finally
{
mycmd.Dispose();
Connection.Close();
}
}

/// <summary>
/// 返回一个object类型的数据，以方便后期的处理
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static object ExecuteSqlValueOfObj(string strSQL)
{
ConnInit();
Connection.Open();
OleDbCommand mycmd=new OleDbCommand(strSQL,Connection);
try
{
object r=mycmd.ExecuteScalar();
if(Object.Equals(r,null))
{
throw new Exception("value unavailable");
}
else
{
return r;
}
}
catch
{
//throw new Exception(e.Message);
return null;

}
finally
{
mycmd.Dispose();
Connection.Close();
}
}

/// <summary>
/// "批量执行"
///这个函数是执行多条无返回值的sql数据库操作，他的参数是一个数组，每一个字符串是一个sql数据库操作的命令，
///如果都执行成功就返回0。需要注意的是，他用到了sqlTransaction，先连续执行完字符数组指定的一系列的操作，然活再提交
///，如果这个过程出现异常，则就回滚到原来的状态
/// </summary>
/// <param name="strSQLs"></param>
/// <returns></returns>

public static int ExecuteSqls(string[] strSQLs)
{
ConnInit();
Connection.Open();
OleDbCommand mycmd=new OleDbCommand();
int j=strSQLs.Length;
OleDbTransaction myTrans=Connection.BeginTransaction();//新建一个事务
try
{
mycmd.Connection=Connection;
mycmd.Transaction=myTrans;//指定Transaction属性为新建的myTrans
foreach(string str in strSQLs)
{
mycmd.CommandText=str;
mycmd.ExecuteNonQuery();//执行数据库的命令
}
myTrans.Commit();
return 1;
}
catch
{
myTrans.Rollback();
return 0;
//证明失败了
//throw new Exception(e.Message);
}
finally
{
mycmd.Dispose();
Connection.Close();
}
}

/// <summary>
/// 重载的一个方法
/// </summary>
/// <param name="strSQLs"></param>
/// <returns></returns>

public static int ExecuteSqls(System.Collections.ArrayList strSQLs)
{
ConnInit();
Connection.Open();
OleDbCommand mycmd=new OleDbCommand();
int j=strSQLs.Count;
OleDbTransaction myTrans=Connection.BeginTransaction();//新建一个事务
try
{
mycmd.Connection=Connection;
mycmd.Transaction=myTrans;//指定Transaction属性为新建的myTrans
foreach(String str in strSQLs)
{
mycmd.CommandText=str;
mycmd.ExecuteNonQuery();//执行数据库的命令
}
myTrans.Commit();
return 1;
}
catch
{
myTrans.Rollback();
return 0;
//证明失败了
//throw new Exception(e.Message);
}
finally
{
mycmd.Dispose();
Connection.Close();
}
}

/// <summary>
/// 执行多条SQL语句，实现数据库事务，每条语句以“;”分割。
/// </summary>
/// <param name="SQLStringList"></param>
public static int ExecuteSqls(string SQLStringList)
{
ConnInit();
SQLStringList=SQLStringList.Replace("；",";");

Connection.Open();
OleDbCommand cmd=new OleDbCommand();
cmd.Connection=Connection;
OleDbTransaction tx=Connection.BeginTransaction();
cmd.Transaction=tx;
try
{
string [] split= SQLStringList.Split(new Char [] { ';'});
foreach (string strsql in split)
{
if (strsql.Trim()!="")
{
cmd.CommandText=strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
return 1;
}
catch(System.Data.Odbc.OdbcException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
Connection.Close();
}

}


/// <summary>
/// 关闭数据库联接
/// </summary>
public static void Close()
{
Connection.Close();
}
}
}****************************

 建一个DB类,在DB类里建一个方法:
Public Sub Execute(ByVal sqlstr As String)
Dim con As New SqlConnection("server=.;database=Data;uid=sa;pwd=;")//数据库连接
con = con.Open()
Dim cmd As New SqlCommand(sqlstr, con)
Try
cmd.ExecuteNonQuery()
Catch ex As Exception

End Try
End Sub
///插入数据
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim DB as new DB()///引用DB类
Dim str as string="insert into liu(liu) values('liu')"
DB.Execute(str)///调用类里的方法
End Sub
///更新数据
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim DB as new DB()///引用DB类
Dim str as string="update liu set liu='feng' where id=1"
DB.Execute(str)///调用类里的方法
End Sub
///删除数据
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim DB as new DB()///引用DB类
Dim str as string="delete from liu where id=1"
DB.Execute(str)///调用类里的方法
End Sub
///在Data建立一个liu的表,在表里有两个字段:id,liu;其中id是自动生成的!

****************************************

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace Mysqlserver
{
///
/// SqlServerDataBase 的摘要说明
///
public class SqlServerDataBase
{
private string strError = null;
private int intCount = 0;
public SqlServerDataBase()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
///
/// 公开方法DBConn，返回数据库连接
///
///
public SqlConnection DBconn()
{
string strConn = "Server=(local);Database=GlobalMeetings;Uid=sa;pwd=";
try
{
return new SqlConnection(strConn);
}
catch (Exception)
{
return null;
}
}
///
/// 公开属性ErrorMessage，返回错误信息
///
public string ErrorMessage
{
get
{
return strError;
}
}

///
/// 根据查询语句从数据库检索数据
///
/// 查询语句
/// 数据库连接
/// 有数据则返回DataSet对象，否则返回null
public DataSet Select(string SelectString, SqlConnection sqlConn)
{
strError = "";
SqlConnection conn;
if (sqlConn == null)
{
conn = DBconn();
}
else
{
conn = sqlConn;
}
try
{
//若数据库连接的当前状态是关闭的，则打开连接
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
SqlCommand selectCommand = new SqlCommand(SelectString, conn);
selectCommand.CommandType = CommandType.Text;
mySqlDataAdapter.SelectCommand = selectCommand;
DataSet myDS = new DataSet();
mySqlDataAdapter.Fill(myDS);
return myDS;
}
catch (Exception e)
{
strError = "数据检索失败：" e.Message;
return null;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
///
/// 更新数据库
///
/// Update Sql语句
/// 数据库连接
/// 更新成功返回true
public bool Update(string UpdateString, SqlConnection SqlConn)
{
return udiDataBase(UpdateString, SqlConn);
}
///
/// 从数据库中删除数据
///
/// Delete Sql语句
/// 数据库连接
/// 删除成功返回true
public bool Delete(string DeleteString, SqlConnection SqlConn)
{
return udiDataBase(DeleteString, SqlConn);
}
///
/// 把数据插入数据库
///
/// Insert Sql语句
/// 数据库连接
/// 插入成功返回true
public bool Insert(string InsertString, SqlConnection SqlConn)
{
return udiDataBase(InsertString, SqlConn);
}
///
/// 根据Sql语句更新数据库
///
/// 更新语句
/// 数据库连接
/// 更新成功则返回true
public bool udiDataBase(string UDIString, SqlConnection SqlConn)
{
strError = "";
SqlConnection conn;
if (SqlConn == null)
{
conn = DBconn();
}
else
{
conn = SqlConn;
}
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand(UDIString, conn);
cmd.CommandType = CommandType.Text;
intCount = cmd.ExecuteNonQuery();
return !(intCount < 1);
}
catch (Exception e)
{
strError = "更新数据库失败：" e.Message;
return false;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
}
-----------------------------
两种调用方法
1、 string strUserPsw = UserPsw.Text.Trim();
string UserPassword = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(strUserPsw, "MD5");//md5加密
SqlServerDataBase obj = new SqlServerDataBase();
obj.Insert("insert into asUserInfo (UserName,UserPassword,Question,Answer,CreateTime) values('" UserName.Text.Trim() "','" UserPassword "','" Question.Text.Trim() "','" Answer.Text.Trim() "','" DateTime.Now.ToString() "' )", null);
2、 private bool IsUsernameExist(string strUsername)
{
bool bRet = true;
SqlServerDataBase db = new SqlServerDataBase();
DataSet ds = db.Select("select * from asUserInfo where UserName = '" strUsername "'", null);
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
bRet = false;
}
else
{
bRet = true;
}

return bRet;
}