using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
namespace CardTest1
{
public class DB
{
public DB()
{
}
private SqlConnection conn;//连接对象
private SqlCommand cmd;//命令对象
private SqlDataAdapter sda;//适配器对象
private SqlDataReader dr;//数据读取器对象
private DataSet ds;//数据集对象
private bool m_bool;
public DB(string connectionString)
{
conn = new SqlConnection(connectionString);
}
//###########从文件里读取连接字符串################
public string readerConnectionString()
{
string connectionString="";
StreamReader sr = new StreamReader("connection.txt", Encoding.Default);
try
{
connectionString = sr.ReadToEnd();
}
catch { }
finally
{
sr.Close();
}
return connectionString;
}
public bool isExist(string sql, string table)
{
sda = new SqlDataAdapter(sql, conn);
ds = new DataSet();
try
{
int n = sda.Fill(ds, table);
if (n > 0)
{
m_bool = true;
}
else
{
m_bool = false;
}
}
catch { }
return m_bool;
}
//################通过传过来的sql语句,返回数据读取器对象#########
public SqlDataReader dataReader(string sql)
{
cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
dr = cmd.ExecuteReader();
}
catch { }
return dr;
}
//##########执行数据,包括插入,删除,修改##########
public bool excuteData(string sql)
{
cmd = new SqlCommand(sql, conn);
try
{
int n = cmd.ExecuteNonQuery();
if (n > 0)
{
m_bool = true;
}
else
{
m_bool = false;
}
}
catch { }
finally
{
conn.Close();
}
return m_bool;
}
//###################返回数据集#######################
public DataSet dataSet(string sql, string table)
{
sda = new SqlDataAdapter(sql, conn);
ds = new DataSet();
int n = sda.Fill(ds, table);
if (n > 0)
{
return ds;
}
else
{
return null;
}
}
public bool isExistProcudure(string sql, SqlParameter[] param)
{
cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
cmd.Parameters.Add(parameter);
}
}
try
{
conn.Open();
int n = Convert.ToInt32(cmd.ExecuteScalar());
if (n >= 0)
{
m_bool = true;
}
else
{
m_bool = false;
}
}
catch { }
finally
{
conn.Close();
}
return m_bool;
}
//##########执行返回查询结果的存储过程#########
public SqlDataReader dataReaderProcedure(string procedureName, SqlParameter[] param)
{
cmd = new SqlCommand(procedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
cmd.Parameters.Add(parameter);
}
}
try
{
conn.Open();
dr = cmd.ExecuteReader();
}
catch { }
return dr;
}
//#############通过传过来的存储过程,返回数据集##################
public DataSet dataSetPrucedure(string procdureName, string table)
{
cmd = new SqlCommand(procdureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
ds = new DataSet();
int n = sda.Fill(ds, table);
if (n > 0)
{
return ds;
}
else
{
return null;
}
}
//#############执行更新,返回布尔型的存储过程#####
public bool isSuccessfulPrucedure(string procedureName, SqlParameter[] param)
{
cmd = new SqlCommand(procedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (param != null)
{
foreach (SqlParameter parameter in param)
{
cmd.Parameters.Add(parameter);
}
}
try
{
conn.Open();
int n = cmd.ExecuteNonQuery();
if (n > 0)
{
m_bool = true;
}
else
{
m_bool = false;
}
}
catch { }
finally
{
conn.Close();
}
return m_bool;
}
public void open()
{
if (conn.State == ConnectionState.Closed)
{
try
{
conn.Open();
}
catch { }
}
}
public void close()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}