using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace EmployerProject
{
class DbUtil
{
public SqlConnection getConnection()
{
String conStr=ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = conStr;
return conn;
}
//select* from tb_EmployeeGenre where id=@id
/// <summary>
/// 执行的是类似select *的语句返回多行多列数据
/// </summary>
/// <param name="sql"></param>
/// <param name="tableName"></param>
/// <param name="paras"></param>
/// <returns></returns>
public DataSet executeDataSet(String sql,string tableName,SqlParameter[] paras)
{
DataSet ds = new DataSet();
using (SqlConnection conn = getConnection())
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
comm.Connection = conn;
if(paras!=null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = comm;
adapter.Fill(ds,tableName);
conn.Close();
}
return ds;
}
/// <summary>
/// 执行的是Insert,update,delete语句
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public bool executeUpdate(String sql, SqlParameter[] paras)
{
int count=0;
using (SqlConnection conn = getConnection())
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
comm.Connection = conn;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
count = comm.ExecuteNonQuery();
conn.Close();
}
return count>0;
}
/// <summary>
/// 用于执行select count(),avg()带聚合函数的SQL语句
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public object executeScalar(String sql, SqlParameter[] paras)
{
object o;
using (SqlConnection conn = getConnection())
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
comm.Connection = conn;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
o = comm.ExecuteScalar();
conn.Close();
}
return o;
}
}
}