小编发现以上这种封装方式还是很麻烦,每次对SQL进行增删改查的时候还得先打开数据库,最后还要关闭,实际运用起来比较麻烦。因此对上面两个增删改查的方法进行了重载,在每次进行操作时都先打开数据库,然后关闭数据库。
/// <summary>
/// SQLserver insert,delete,update
/// </summary>
/// <param name="sql">insert,delete,update statement</param>
/// <param name="link">link statement</param>
/// <returns>Success:success + Number of affected rows; Fail:reason</returns>
public string Sqlserver_Insdelupd(string sql, string link)
{
try
{
int num = 0;
using (SqlConnection con = new SqlConnection(link))
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
num = cmd.ExecuteNonQuery();
con.Close();
return "success" + num;
}
}
catch (Exception ex)
{
return ex.Message.ToString();
}
}
/// <summary>
/// SQLserver select
/// </summary>
/// <param name="sql">select statement</param>
/// <param name="link">link statement</param>
/// <param name="record">Success:success; Fail:reason</param>
/// <returns>select result</returns>
public DataSet Sqlserver_Select(string sql, string link, out string record)
{
try
{
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(link))
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter(sql, con);
sda.Fill(ds, "sample");
con.Close();
sda.Dispose();
record = "success";
return ds;
}
}
catch (Exception ex)
{
DataSet dataSet = new DataSet();
record = ex.Message.ToString();
return dataSet;
}
}