.NET 三层架构中放在DAL层的DBHelper

?using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace DAL
{
public class DBHelper
{
public static SqlConnection CreateConnection()
{
//return new SqlConnection(@"server=.\sqlexperss;database=jiajiaodb;user Id=sa;pwd=123456");
return new SqlConnection("Data Source=.;Initial Catalog=Myoffice;Persist Security Info=True;User ID=sa;Password=sa");
}
public static SqlCommand CreateCommand(string sql)
{
return new SqlCommand(sql, CreateConnection());
}
public static SqlCommand CreateCommand(string sql, SqlParameter sp)
{
SqlCommand command=new SqlCommand(sql,CreateConnection());
command.Parameters.Add(sp);
return command;
}
public static SqlCommand CreateCommand(string sql, SqlParameter[] sps)
{
SqlCommand command = new SqlCommand(sql, CreateConnection());
command.Parameters.AddRange(sps);
return command;
}
//没有带参数的返回受影响行数SQL语句
public static int ExecuteNonQuery(string sql)
{
SqlCommand command = null;
try
{
command = CreateCommand(sql);
command.Connection.Open();
return command.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (command != null)
{
command.Connection.Close();
}
}
return 0;
}
//带一个参数的返回受影响行数SQL语句
public static int ExecuteNonQuery(string sql, SqlParameter sp)
{
SqlCommand command = null;
try
{
command = CreateCommand(sql, sp);
command.Connection.Open();
return command.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (command != null)
{
command.Connection.Close();
}
}
return 0;
}
//带多个参数的返回受影响行数SQL语句
public static int ExecuteNonQuery(string sql, SqlParameter[] sps)
{
SqlCommand command = null;
try
{
command = CreateCommand(sql, sps);
command.Connection.Open();
return command.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);

}
finally
{
if (command != null)
{
command.Connection.Close();
}
}
return 0;
}
//没有带参数的返回第一行第一列的值SQL语句
public static object ExecuteScalar(string sql)
{
SqlCommand command = null;
try
{
command = CreateCommand(sql);
command.Connection.Open();
return command.ExecuteScalar();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (command != null)
{
command.Connection.Close();
}
}
return 0;
}
//带一个参数的返回第一行第一列的值SQL语句
public static object ExecuteScalar(string sql, SqlParameter sp)
{
SqlCommand command = null;
try
{
command = CreateCommand(sql, sp);
command.Connection.Open();
return command.ExecuteScalar();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (command != null)
{
command.Connection.Close();
}
}
return 0;
}
//第多个参数的返回第一行第一列的值SQL语句
public static object ExecuteScalar(string sql, SqlParameter[] sps)
{
SqlCommand command = null;
try
{
command = CreateCommand(sql, sps);
command.Connection.Open();
return command.ExecuteScalar();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (command != null)
{
command.Connection.Close();
}
}
return 0;
}
//没有带参数的返回多行多列的值SQL语句
public static SqlDataReader ExecuteReader(string sql)
{
SqlCommand command = null;
try
{
command = CreateCommand(sql);
command.Connection.Open();
return command.ExecuteReader(https://www.360docs.net/doc/4e5256686.html,mandBehavior.CloseConnection);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
return null;
}
//带一个参数的返回多行多列的值SQL语句
public static SqlDataReader ExecuteReader(str

ing sql,SqlParameter sp)
{
SqlCommand command = null;
try
{
command = CreateCommand(sql,sp);
command.Connection.Open();
return command.ExecuteReader(https://www.360docs.net/doc/4e5256686.html,mandBehavior.CloseConnection);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
return null;
}
//带多个参数的返回多行多列的值SQL语句
public static SqlDataReader ExecuteReader(string sql,SqlParameter[] sps)
{
SqlCommand command = null;
try
{
command = CreateCommand(sql,sps);
command.Connection.Open();
return command.ExecuteReader(https://www.360docs.net/doc/4e5256686.html,mandBehavior.CloseConnection);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
return null;
}
}
}