sqlserver的 处理类
using System;
using System.Data;
using System.Data.SqlClient;
namespace myNovelSite
{
/// <summary>
/// Summary description for ManageData.
/// </summary>
public class ManageData
{
private SqlConnection m_conn = null;
private string m_ConnStr = null; //连接字符串
private string m_ServerName = null; //服务器名
private string m_DatabaseName = null; //数据库名
private string m_UserName = null; //用户名
private string m_Password = null; //密码
private string m_error = ""; //错误返回
/// <param name="ServerName">服务器名称</param>
/// <param name="DatabaseName">数据库名称</param>
/// <param name="UserName">用户名</param>
/// <param name="Password">密码</param>
public ManageData (string ServerName, string DatabaseName, string UserName, string Password)
{
m_ServerName = ServerName;
m_DatabaseName = DatabaseName;
m_UserName = UserName;
m_Password = Password;
m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
m_conn = new SqlConnection (m_ConnStr);
}
/// <summary>
///
/// </summary>
/// <param name="ConnStr">连接字符串</param>
public ManageData(string ConnStr)
{
m_ConnStr = ConnStr;
m_conn = new SqlConnection (ConnStr);
}
/// <summary>
///
/// </summary>
public ManageData()
{
m_conn = new SqlConnection ();
}
~ManageData ()
{
// this.Close ();
}
#region Public Properties
/// <summary>
/// 连接字符串
/// </summary>
public string ConnectionString
{
get
{
return m_ConnStr;
}
set
{
m_ConnStr = value;
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 服务器名
/// </summary>
public string ServerName
{
get
{
return m_ServerName;
}
set
{
m_ServerName = value;
m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 数据库名
/// </summary>
public string DatabaseName
{
get
{
return m_DatabaseName;
}
set
{
m_DatabaseName = value;
m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 用户名
/// </summary>
public string UserName
{
get
{
return m_UserName;
}
set
{
m_UserName = value;
m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 密码
/// </summary>
public string Password
{
get
{
return m_Password;
}
set
{
m_Password = value;
m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
#endregion
#region Public Methods
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close ()
{
// if (m_conn.State == ConnectionState.Closed)
// {
m_conn.Open ();
// }
if (m_conn!=null && (m_conn.State ==ConnectionState.Open))
{
m_conn.Close(); // 关闭数据库
}
}
public SqlDataReader ExecReader (string sText)
{
SqlDataReader dr = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
SqlCommand cmd = new SqlCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader ();
}
catch (Exception ex)
{
string s = ex.Message;
}
return dr;
}
public SqlCommand ExecSqlCommand(string sText)
{
if(m_conn.State==ConnectionState.Closed)
m_conn.Open();
SqlCommand sqlcom=new SqlCommand(sText,m_conn);
return sqlcom;
}
/// <summary>
/// 执行查询,并返回查询所返回的结果集中的第一行的第一列.忽略额外的行或列.
/// </summary>
/// <param name="sText">查询语句</param>
/// <returns>返回查询所返回的结果集中的第一行的第一列</returns>
public object ExecuteScalar(string sText)
{
object obj = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open();
}
SqlCommand cmd = new SqlCommand(sText,m_conn);
cmd.CommandType = CommandType.Text;
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
m_conn.Close ();
}
return obj;
}
/// <summary>
/// 通过SQL查询得到数据集
/// </summary>
/// <param name="sText">sql查询字符串</param>
/// <returns></returns>
public DataSet ExecDS (string sText)
{
DataSet ds = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
SqlDataAdapter da = new SqlDataAdapter (sText, m_conn);
ds = new DataSet ();
da.Fill (ds);
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
m_conn.Close ();
}
return ds;
}
/// <summary>
/// 通过SQL查询得到数据适配器
/// </summary>
/// <param name="sText"></param>
/// <returns></returns>
public SqlDataAdapter ExecDA(string sText)
{
SqlDataAdapter da = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
da = new SqlDataAdapter (sText, m_conn);
//
// dt = new DataTable ();
// da.Fill (dt);
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
m_conn.Close ();
}
return da;
}
/// <summary>
/// 通过SQL查询得到数据表
/// </summary>
/// <param name="sText"></param>
/// <returns></returns>
public DataTable ExecDT (string sText)
{
DataTable dt = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
SqlDataAdapter da = new SqlDataAdapter (sText, m_conn);
dt = new DataTable ();
da.Fill (dt);
}
catch (Exception ex)
{
string s = ex.Message;
throw (new Exception(s));
}
finally
{
m_conn.Close ();
}
return dt;
}
public bool ExecSqlQuery (string sql)
{
int iRowsAffected =-1;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
SqlCommand cmd = new SqlCommand (sql, m_conn);
cmd.CommandType = CommandType.Text;
iRowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string s = ex.Message;
if(ex!=null)
{
return false;
}
}
finally
{
m_conn.Close ();
}
if(iRowsAffected!=-1)
{
return true;
}
else
{
return false;
}
}
public int ExecNonQuery (string sText)
{
int iRowsAffected = -1;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
SqlCommand cmd = new SqlCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
iRowsAffected = cmd.ExecuteNonQuery ();
}
catch (Exception ex)
{
string s = ex.Message;
m_error=ex.Message;
if(ex!=null)
{
return 0;
}
}
finally
{
m_conn.Close ();
}
return iRowsAffected;
}
}
}
#endregion
using System;
using System.Data;
using System.Data.SqlClient;
namespace myNovelSite
{
/// <summary>
/// Summary description for ManageData.
/// </summary>
public class ManageData
{
private SqlConnection m_conn = null;
private string m_ConnStr = null; //连接字符串
private string m_ServerName = null; //服务器名
private string m_DatabaseName = null; //数据库名
private string m_UserName = null; //用户名
private string m_Password = null; //密码
private string m_error = ""; //错误返回
/// <param name="ServerName">服务器名称</param>
/// <param name="DatabaseName">数据库名称</param>
/// <param name="UserName">用户名</param>
/// <param name="Password">密码</param>
public ManageData (string ServerName, string DatabaseName, string UserName, string Password)
{
m_ServerName = ServerName;
m_DatabaseName = DatabaseName;
m_UserName = UserName;
m_Password = Password;
m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
m_conn = new SqlConnection (m_ConnStr);
}
/// <summary>
///
/// </summary>
/// <param name="ConnStr">连接字符串</param>
public ManageData(string ConnStr)
{
m_ConnStr = ConnStr;
m_conn = new SqlConnection (ConnStr);
}
/// <summary>
///
/// </summary>
public ManageData()
{
m_conn = new SqlConnection ();
}
~ManageData ()
{
// this.Close ();
}
#region Public Properties
/// <summary>
/// 连接字符串
/// </summary>
public string ConnectionString
{
get
{
return m_ConnStr;
}
set
{
m_ConnStr = value;
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 服务器名
/// </summary>
public string ServerName
{
get
{
return m_ServerName;
}
set
{
m_ServerName = value;
m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 数据库名
/// </summary>
public string DatabaseName
{
get
{
return m_DatabaseName;
}
set
{
m_DatabaseName = value;
m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 用户名
/// </summary>
public string UserName
{
get
{
return m_UserName;
}
set
{
m_UserName = value;
m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
/// <summary>
/// 密码
/// </summary>
public string Password
{
get
{
return m_Password;
}
set
{
m_Password = value;
m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
m_conn.ConnectionString = m_ConnStr;
}
}
#endregion
#region Public Methods
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close ()
{
// if (m_conn.State == ConnectionState.Closed)
// {
m_conn.Open ();
// }
if (m_conn!=null && (m_conn.State ==ConnectionState.Open))
{
m_conn.Close(); // 关闭数据库
}
}
public SqlDataReader ExecReader (string sText)
{
SqlDataReader dr = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
SqlCommand cmd = new SqlCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader ();
}
catch (Exception ex)
{
string s = ex.Message;
}
return dr;
}
public SqlCommand ExecSqlCommand(string sText)
{
if(m_conn.State==ConnectionState.Closed)
m_conn.Open();
SqlCommand sqlcom=new SqlCommand(sText,m_conn);
return sqlcom;
}
/// <summary>
/// 执行查询,并返回查询所返回的结果集中的第一行的第一列.忽略额外的行或列.
/// </summary>
/// <param name="sText">查询语句</param>
/// <returns>返回查询所返回的结果集中的第一行的第一列</returns>
public object ExecuteScalar(string sText)
{
object obj = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open();
}
SqlCommand cmd = new SqlCommand(sText,m_conn);
cmd.CommandType = CommandType.Text;
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
m_conn.Close ();
}
return obj;
}
/// <summary>
/// 通过SQL查询得到数据集
/// </summary>
/// <param name="sText">sql查询字符串</param>
/// <returns></returns>
public DataSet ExecDS (string sText)
{
DataSet ds = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
SqlDataAdapter da = new SqlDataAdapter (sText, m_conn);
ds = new DataSet ();
da.Fill (ds);
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
m_conn.Close ();
}
return ds;
}
/// <summary>
/// 通过SQL查询得到数据适配器
/// </summary>
/// <param name="sText"></param>
/// <returns></returns>
public SqlDataAdapter ExecDA(string sText)
{
SqlDataAdapter da = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
da = new SqlDataAdapter (sText, m_conn);
//
// dt = new DataTable ();
// da.Fill (dt);
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
m_conn.Close ();
}
return da;
}
/// <summary>
/// 通过SQL查询得到数据表
/// </summary>
/// <param name="sText"></param>
/// <returns></returns>
public DataTable ExecDT (string sText)
{
DataTable dt = null;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
SqlDataAdapter da = new SqlDataAdapter (sText, m_conn);
dt = new DataTable ();
da.Fill (dt);
}
catch (Exception ex)
{
string s = ex.Message;
throw (new Exception(s));
}
finally
{
m_conn.Close ();
}
return dt;
}
public bool ExecSqlQuery (string sql)
{
int iRowsAffected =-1;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
SqlCommand cmd = new SqlCommand (sql, m_conn);
cmd.CommandType = CommandType.Text;
iRowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string s = ex.Message;
if(ex!=null)
{
return false;
}
}
finally
{
m_conn.Close ();
}
if(iRowsAffected!=-1)
{
return true;
}
else
{
return false;
}
}
public int ExecNonQuery (string sText)
{
int iRowsAffected = -1;
try
{
if (m_conn.State == ConnectionState.Closed)
{
m_conn.Open ();
}
SqlCommand cmd = new SqlCommand (sText, m_conn);
cmd.CommandType = CommandType.Text;
iRowsAffected = cmd.ExecuteNonQuery ();
}
catch (Exception ex)
{
string s = ex.Message;
m_error=ex.Message;
if(ex!=null)
{
return 0;
}
}
finally
{
m_conn.Close ();
}
return iRowsAffected;
}
}
}
#endregion