Ado.net中简单的DBHelper类（增删改查）

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

private static string connString = "server=.;database=hotel;uid=aa;pwd=123";//最好从配置文件中取出

public static SqlConnection conn = new SqlConnection(connString);

在配置文件中进行配置并取出

<?xml version="1.0" encoding="utf-8"?>

<add name="Test"

connectionString="Data Source=.;Initial Catalog=HospitalInstrument;User ID=sa;Password=123"

providerName="System.Data.SqlClient"/>

</connectionStrings>

</configuration>

添加引用System.Configuration

使用该命名空间下的ConfigrationManager类

string constr=ConfigurationManager.ConnectionStrings["Test"].ConnectionString;

//公用的增删改方法
public static bool ZhengShanGai(string sql)
{
bool flag = false;
SqlCommand com = null;
try
{
if (sql == null || sql == "")
{
return false;
}
if (conn.State != ConnectionState.Open)
conn.Open();
com = new SqlCommand(sql, conn);

if (com.ExecuteNonQuery() > 0)
flag = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
com.Dispose();
conn.Close();
}
return flag;
}
//公用的查询方法 ,返回值为SqlDateReader
public static SqlDataReader Query(string sql)
{
SqlCommand com = null;
SqlDataReader reader = null;
try
{
if (sql == null || sql == "")
{
return null;
}
if (conn.State != ConnectionState.Open)
conn.Open();
com = new SqlCommand(sql, conn);
//当关闭reader后连接自动关闭
reader = com.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
return null;
throw ex;
}
return reader;
}
//公用的查询方法 ,返回值为DataTable;
public static DataTable QueryInfo(string sql)
{
DataTable dt = new DataTable();
SqlDataAdapter da = null;
try
{
DBHelper.conn.Open();
da = new SqlDataAdapter(sql, DBHelper.conn);
da.Fill(dt);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
DBHelper.conn.Close();
}
return dt;
}
// 执行带参数存储过程的方法
public bool ExcuteProc(string procName, SqlParameter[] procParams)
{
bool flag = false;
SqlCommand com = null;
try
{
if (procName == null || procName == "") return false;
if (conn.State != ConnectionState.Open) conn.Open();
com = new SqlCommand(procName, conn);
com.CommandType = CommandType.StoredProcedure;
if (procParams != null)
{
foreach (SqlParameter procParam in procParams)
{
com.Parameters.Add(procParam);
}
}
if (com.ExecuteNonQuery() > 0) flag = true;
}
catch (Exception)
{
return false;
}
finally
{
com.Dispose();
conn.Close();
}
return flag;
}
}