平时在进行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
/// <summary>
/// 执行sql语句返回数据表
/// </summary>
/// <param name="CmdText"></param>
/// <returns></returns>
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
/// <summary>
/// 通过表名获取数据表
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public DataTable GetTableByTableName(String TableName)
{
return GetTableBySql(String.Format("select * from {0}", TableName));
}
#endregion
#region
/// <summary>
///
/// 执行存储返回数据表
/// </summary>
/// <param name="ProcedureName"></param>
/// <param name="Paramters"></param>
/// <returns></returns>
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
/// <summary>
/// 执行存储返回数据集
/// </summary>
/// <param name="ProcedureName"></param>
/// <param name="Paramters"></param>
/// <returns></returns>
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
/// <summary>
/// 执行sql语句，返回影响的记录数
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
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
/// <summary>
/// 执行存储返回字符串
/// </summary>
/// <param name="ProcedureName"></param>
/// <param name="Paramters"></param>
/// <returns></returns>
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
/// <summary>
/// 执行查询语句，返回SqlDataReader
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
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];
}如有不当之处，请多多指教！