平时在进行C#开发时，需要对数据库进行操作，下面介绍几种常见的操作数据库的方法：
一、操作类DataAccess
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DevExpress.XtraEditors;
using System.Windows.Forms;
//自己写的解密数据库链接dll，可以忽略
using ConnDatabase;
namespace OperatDatabase
{
public class DataAccess
{
private static DataAccess _Access;
public static DataAccess Access()
{
if( _Access==null )
{
_Access=new DataAccess();
}
return _Access;
}
//数据库链接，这里我用了配置文件DataSourse.ini，将链接信息进行了加密
public static string sConnect=GetRasDatacase.RSADecrypt(GetRasDatacase.ReadProfileString(@".\DataSourse.ini", "DataSourse", "DataSourse_oa", ""));
//数据库链接，比较直接的方式，安全性不高
// public static string sConnect=string.Format("server={0};database={1};uid={2}; pwd={3};", "数据库ip", "数据库名称", "用户名", "密码");
public static SqlConnection _Connect=new SqlConnection(sConnect);
#region
///
/// 执行sql语句返回数据表
///
///
///
public DataTable GetTableBySql(String CmdText)
{
SqlConnection SqlCon=_Connect;
SqlDataAdapter SqlAdp=null;
DataTable dtTalble=null;
try
{
if (SqlCon !=null && SqlCon.State !=ConnectionState.Open)
{
SqlCon.Open();
}
SqlAdp=new SqlDataAdapter(CmdText, SqlCon);
dtTalble=new DataTable();
SqlAdp.Fill(dtTalble);
return dtTalble;
}
catch (Exception ex)
{
XtraMessageBox.Show("获取DataTable时发生错误：" + ex.Message, "警告！", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
finally
{
if (SqlCon !=null && SqlCon.State !=ConnectionState.Closed)
{
SqlCon.Close();
}
}
}
#endregion
#region
///
/// 通过表名获取数据表
///
///
///
public DataTable GetTableByTableName(String TableName)
{
return GetTableBySql(String.Format("select * from {0}", TableName));
}
#endregion
#region
///
///
/// 执行存储返回数据表
///
///
///
///
public DataTable GetTableByProcedure(String ProcedureName, SqlParameter[] Paramters)
{
SqlConnection SqlCon=null;
SqlCommand SqlCmd=null;
SqlDataAdapter SqlAdp=null;
DataTable dtData=null;
try
{
SqlCon=_Connect;
if (SqlCon !=null && SqlCon.State !=ConnectionState.Open)
{
SqlCon.Open();
}
SqlCmd=new SqlCommand();
SqlCmd.Connection=SqlCon;
SqlCmd.CommandType=CommandType.StoredProcedure;
SqlCmd.CommandText=ProcedureName;
String ParametersName=String.Empty;
String ParametersValue=String.Empty;
SqlDbType ParamtersType=SqlDbType.Char;
foreach (SqlParameter Paramter in Paramters)
{
ParametersName=Paramter.ParameterName;
ParametersValue=Convert.ToString(Paramter.Value);
ParamtersType=Paramter.SqlDbType;
SqlCmd.Parameters.Add(ParametersName, Paramter.SqlDbType);
SqlCmd.Parameters[ParametersName].Value=ParametersValue;
}
SqlAdp=new SqlDataAdapter(SqlCmd);
dtData=new DataTable();
SqlAdp.Fill(dtData);
return dtData;
}
catch (Exception ex)
{
return null;
}
finally
{
if (SqlCon !=null && SqlCon.State !=ConnectionState.Closed)
{
SqlCon.Close();
}
}
}
#endregion
#region
///
/// 执行存储返回数据集
///
///
///
///
public DataSet GetDataSetByProcedure(String ProcedureName, SqlParameter[] Paramters)
{
SqlConnection SqlCon=null;
SqlCommand SqlCmd=null;
SqlDataAdapter SqlAdp=null;
DataSet dtData=null;
try
{
SqlCon=_Connect;
if (SqlCon !=null && SqlCon.State !=ConnectionState.Open)
{
SqlCon.Open();
}
SqlCmd=new SqlCommand();
SqlCmd.Connection=SqlCon;
SqlCmd.CommandType=CommandType.StoredProcedure;
SqlCmd.CommandText=ProcedureName;
String ParametersName=String.Empty;
String ParametersValue=String.Empty;
SqlDbType ParamtersType=SqlDbType.Char;
foreach (SqlParameter Paramter in Paramters)
{
ParametersName=Paramter.ParameterName;
ParametersValue=Convert.ToString(Paramter.Value);
ParamtersType=Paramter.SqlDbType;
SqlCmd.Parameters.Add(ParametersName, Paramter.SqlDbType);
SqlCmd.Parameters[ParametersName].Value=ParametersValue;
}
SqlAdp=new SqlDataAdapter(SqlCmd);
dtData=new DataSet();
SqlAdp.Fill(dtData);
return dtData;
}
catch (Exception)
{
return null;
}
finally
{
if (SqlCon !=null && SqlCon.State !=ConnectionState.Closed)
{
SqlCon.Close();
}
}
}
#endregion
#region
///
/// 执行sql语句，返回影响的记录数
///
///
///
public int ExecuteSql(String sqlString)
{
using (SqlConnection connection=new SqlConnection(sConnect))
{
using (SqlCommand cmd=new SqlCommand(sqlString, connection))
{
try
{
connection.Open();
int rows=cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
}
}
}
#endregion
#region
///
/// 执行存储返回字符串
///
///
///
///
public String GetStringByProcedure(String ProcedureName, SqlParameter[] Paramters)
{
SqlConnection SqlCon=null;
SqlCommand SqlCmd=null;
SqlDataAdapter SqlAdp=null;
DataTable dtData=null;
try
{
SqlCon=_Connect;
if (SqlCon !=null && SqlCon.State !=ConnectionState.Open)
{
SqlCon.Open();
}
SqlCmd=new SqlCommand();
SqlCmd.Connection=SqlCon;
SqlCmd.CommandType=CommandType.StoredProcedure;
SqlCmd.CommandText=ProcedureName;
String ParametersName=String.Empty;
String ParametersValue=String.Empty;
SqlDbType ParamtersType=SqlDbType.Char;
foreach (SqlParameter Paramter in Paramters)
{
ParametersName=Paramter.ParameterName;
ParametersValue=Convert.ToString(Paramter.Value);
ParamtersType=Paramter.SqlDbType;
SqlCmd.Parameters.Add(ParametersName, Paramter.SqlDbType);
SqlCmd.Parameters[ParametersName].Value=ParametersValue;
}
SqlAdp=new SqlDataAdapter(SqlCmd);
dtData=new DataTable();
SqlAdp.Fill(dtData);
return dtData.Rows[0].ItemArray[0].ToString();
}
catch (Exception ex)
{
return "F";
}
finally
{
if (SqlCon !=null && SqlCon.State !=ConnectionState.Closed)
{
SqlCon.Close();
}
}
}
#endregion
#region
///
/// 执行查询语句，返回SqlDataReader
///
///
///
public SqlDataReader ExecuteReader(String strsql)
{
SqlConnection connection=new SqlConnection(sConnect);
{
SqlCommand cmd=new SqlCommand(strsql, connection);
SqlDataReader myreader;
try
{
connection.Open();
myreader=cmd.ExecuteReader();
return myreader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
#endregion
}
}
二、调用
1、调用执行存储返回买二手游戏数据表
DataTable data=DataAccess.Access().GetTableByProcedure("存储名称",
new SqlParameter[]{
new SqlParameter("@入参1",入参值1),
new SqlParameter("@入参2",入参值2),
});
2、执行sql语句返回数据表
String sql=String.Format("select * from t_user where code='{0}'", "1001");
DataTable dt=DataAccess.Access().GetTableBySql(sql);
3、使用SqlDataReader查询单条数据
String sql=String.Format("select name from t_user where idCode='{0}'", "XXXX");
DataTable dt=DataAccess.Access().GetTableBySql(sql);
SqlDataReader red=DataAccess.Access().ExecuteReader(sql);
String name="";
while (red.Read())
{
name=(String)red[0];
}
如有不当之处，请多多指教！
