SqlHelper
configuration:
<appSettings>
<add key="SQLConn" value="data source=[server].com;initial catalog=[db];user id=[id];password=[pssd]"/>
</appSettings>
SqlHelper:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace SmartLockerWS.Class
{
class sqlHelper1
{
public static string GetSqlConnectionString()
{
return ConfigurationManager.AppSettings["SQLConn"].ToString();
}
//适合增删改操作,返回影响条数
public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))
{
using (SqlCommand comm = conn.CreateCommand())
{
try
{
conn.Open();
comm.CommandText = sql;
if(parameters!=null)
comm.Parameters.AddRange(parameters);
return comm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
}
}
//查询操作,返回查询结果中的第一行第一列的值
public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))
{
using (SqlCommand comm = conn.CreateCommand())
{
try
{
conn.Open();
comm.CommandText = sql;
if(parameters!=null)
comm.Parameters.AddRange(parameters);
return comm.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
}
}
//Adapter调整,查询操作,返回DataTable
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, GetSqlConnectionString()))
{
DataTable dt = new DataTable();
if(parameters!=null)
adapter.SelectCommand.Parameters.AddRange(parameters);
adapter.Fill(dt);
return dt;
}
}
public static DataSet ExecuteDataset(string sql, params SqlParameter[] parameters)
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, GetSqlConnectionString()))
{
DataSet ds = new DataSet();
if(parameters!=null)
adapter.SelectCommand.Parameters.AddRange(parameters);
adapter.Fill(ds);
return ds;
}
}
public static SqlDataReader ExecuteReader(string sqlText, params SqlParameter[] parameters)
{
//SqlDataReader要求,它读取数据的时候有,它独占它的SqlConnection对象,而且SqlConnection必须是Open状态
SqlConnection conn = new SqlConnection(GetSqlConnectionString());//不要释放连接,因为后面还需要连接打开状态
SqlCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = sqlText;
if(parameters!=null)
cmd.Parameters.AddRange(parameters);
//CommandBehavior.CloseConnection当SqlDataReader释放的时候,顺便把SqlConnection对象也释放掉
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}
Call function:
try{
string sql = "select a.badge,a.ic_card,b.name,b.email from [asmuser].[dbo].[ATSCardNo] a left join [asmuser].[dbo].[Employee] b on a.badge = b.badge where a.ic_card = @cardno";
SqlParameter para_cardno = new SqlParameter("@cardno", SqlDbType.VarChar, 10);
para_cardno.Value = CardNo;
SqlParameter[] parms = { para_cardno };
DataTable dt = sqlHelper1.ExecuteDataTable(sql, parms);
if (dt.Rows.Count > 0)
{ }
}
catch (Exception ex)
{ }