using System;
using System.Data;
using System.Data.SqlClient;
namespace esdataweb.classes
{
/// <summary>
/// DbAccess 的摘要说明。
/// </summary>
public class DbAccess
{
SqlConnection conn=null;
SqlCommand cmd = null;
public DbAccess()
{
conn = new SqlConnection();
conn.ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["connstring"].ToString();
cmd = new SqlCommand();
cmd.Connection=conn;
}
#region sql
/// <summary>
/// 返回 数据表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable GetTable(string sql)
{
cmd.CommandText=sql;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand=cmd;
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// /返回 指针
///
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public SqlDataReader GetReader(string sql)
{
SqlDataReader dr= null;
cmd.CommandText=sql;
if(conn.State!=ConnectionState.Open)
conn.Open();
dr = cmd.ExecuteReader();
return dr;
}
/// <summary>
/// 返回 影响几行
///
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int GetState(string sql)
{
int count=0;
cmd.CommandText=sql;
if(conn.State!=ConnectionState.Open)
conn.Open();
try
{
count = cmd.ExecuteNonQuery();
}
finally
{
if(conn.State!=ConnectionState.Closed)
conn.Close();
}
return count;
}
/// <summary>
/// 获得第一行 第一列的 数据
///
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public string GetOne(string sql)
{
string str =string.Empty;
cmd.CommandText=sql;
if(conn.State!=ConnectionState.Open)
conn.Open();
try
{
str = Convert.ToString(cmd.ExecuteScalar());
}
finally
{
if(conn.State!=ConnectionState.Closed)
conn.Close();
}
return str;
}
/// <summary>
/// 监测 是否 有记录
///
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool Check(string sql)
{
bool succ= false;
cmd.CommandText=sql;
if(conn.State!=ConnectionState.Open)
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
succ = true;
}
dr.Close();
dr=null;
if(conn.State!=ConnectionState.Closed)
conn.Close();
return succ;
}
/// <summary>
/// sql数据库 事务处理的解决
///
/// </summary>
/// <param name="sqls"></param>
/// <returns></returns>
public int GetTranSql(string[] sqls)
{
if(conn.State!=ConnectionState.Open)
conn.Open();
SqlTransaction ct = conn.BeginTransaction();
bool succ= true;
int i=0;
cmd.Transaction=ct;
foreach(string sql in sqls)
{
cmd.CommandText=sql;
try
{
i++;
cmd.ExecuteNonQuery();
}
catch
{
i=0;
succ=false;
ct.Rollback();
break;
}
}
if(succ)
{
ct.Commit();
}
if(conn.State!=ConnectionState.Closed)
conn.Close();
return i;
}
#endregion sql
#region procdure
/// <summary>
/// 数据表
///
/// </summary>
/// <param name="procname"></param>
/// <param name="pas"></param>
/// <returns></returns>
public DataTable GetTable(string procname,params SqlParameter[] pas)
{
cmd.CommandText=procname;
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Clear();
foreach(SqlParameter pa in pas)
{
cmd.Parameters.Add(pa);
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand=cmd;
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
/// <summary>
/// /返回存储过程状态
///
/// </summary>
/// <param name="procname"></param>
/// <param name="pas"></param>
/// <returns></returns>
public int GetState(string procname,params SqlParameter[] pas)
{
int count=0;
cmd.CommandText=procname;
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Clear();
foreach(SqlParameter pa in pas)
{
cmd.Parameters.Add(pa);
}
if(conn.State!=ConnectionState.Open)
conn.Open();
count=cmd.ExecuteNonQuery();
if(conn.State!=ConnectionState.Closed)
conn.Close();
return count;
}
#endregion
public void connClose()
{
if(conn.State!=ConnectionState.Closed)
conn.Close();
}
}
}