using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data.SqlClient;
/// <summary>
/// DB 的摘要说明
/// </summary>
public class DB
{
#region 公共成员
public static SqlConnection sqlconnection;//定义公共成员
public static readonly string cnstr = "server=fuck\\myds;database=zd_national;User Id=sa;pwd=123456;pooling=false";//数据库连接字符串
public static readonly string cnstr2 = "server=fuck\\myds;database=master;User Id=sa;pwd=123456;pooling=false";//数据库连接字符串
#endregion public DB()
{
//
// TODO: 在此处添加构造函数逻辑
//
} #region 打开数据库连接
/// <summary>
/// 打开数据库连接 返回SqlConnection
/// </summary>
/// <returns></returns>
public static SqlConnection OpenConnection()
{
try
{
sqlconnection = new SqlConnection(cnstr);
sqlconnection.Open();
return sqlconnection;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion #region 关闭数据库连接并释放资源
/// <summary>
/// 关闭数据库连接释放资源
/// </summary>
/// <param name="Conn">数据库连接对象</param>
public static void DisposeConnection(SqlConnection Conn)
{
if (Conn != null)
{
Conn.Close();
Conn.Dispose();
}
}
#endregion #region 执行Sql查询语句
///<summary>
/// 执行Sql查询语句 执行成功返回受影响的行数,执行失败返回-1;
/// </summary>
/// <param name="strSQL">传入的Sql语句</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteSql(string strSQL)
{
SqlConnection conn = OpenConnection();
try
{
SqlCommand comm = new SqlCommand(strSQL, conn);
int val = comm.ExecuteNonQuery(); DisposeConnection(conn);
return val; }
catch (Exception e)
{
DisposeConnection(conn);
throw new Exception(e.Message);
}
}
#endregion #region 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
/// <summary>
/// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
/// </summary>
/// <param name="strSQL">传入的Sql语句</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader getDataReader(string strSQL,SqlConnection cn)
{
SqlDataReader dr = null;
try
{ SqlCommand comm = new SqlCommand(strSQL, cn);
dr = comm.ExecuteReader();
return dr;
}
catch (Exception ex)
{
if (dr != null && !dr.IsClosed)
dr.Close();
DisposeConnection(cn);
throw new Exception(ex.Message);
} }
#endregion #region 返回指定Sql语句的DataTable
/// <summary>
/// 返回指定Sql语句的DataTable
/// </summary>
/// <param name="strSQL">传入的Sql语句</param>
/// <returns>DataTable</returns>
public static DataTable getDataTable(string strSQL)
{
SqlConnection conn = OpenConnection();
try
{
SqlCommand comm = new SqlCommand(strSQL, conn);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataTable table = new DataTable();
da.Fill(table); DisposeConnection(conn);
return table;
}
catch (Exception ex)
{
DisposeConnection(conn);
throw new Exception(ex.Message);
}
}
#endregion #region 返回指定Sql语句的DataSet
/// <summary>
/// 返回指定Sql语句的DataSet
/// </summary>
/// <param name="strSQL">传入的Sql语句</param>
/// <returns>DataSet</returns>
public static DataSet getDataSet(string strSQL)
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlConnection conn = OpenConnection();
try
{
SqlCommand comm = new SqlCommand(strSQL, conn);
comm.CommandType = CommandType.Text;
da.SelectCommand = comm;
da.Fill(ds); DisposeConnection(conn);
return ds;
}
catch (Exception e)
{
DisposeConnection(conn);
throw new Exception(e.Message);
}
}
#endregion #region 查询数据库中是否存在该条数据 存在返回True,不存在返回False
/// <summary>
/// 是否存在值
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static bool isExists(string strSQL)
{
SqlConnection conn = OpenConnection();
try
{
SqlCommand comm = new SqlCommand(strSQL,conn);
SqlDataReader dr = comm.ExecuteReader(); if (dr.HasRows) return true; DisposeConnection(conn);
return false;
}
catch (Exception ex)
{
DisposeConnection(conn);
throw new Exception(ex.Message);
} }
#endregion #region 获取记录总数
/// <summary>
/// 获取查询记录总数
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public static int getRowCount(string tableNm)
{
SqlConnection cn=OpenConnection();
int intRowCount = 0; string str = "select count(*) from (" + tableNm + ")";
SqlCommand cmd=new SqlCommand (str,cn);
intRowCount = (int)cmd.ExecuteScalar();
DisposeConnection(cn);
return intRowCount;
}
#endregion #region 为DropDownList绑定数据
public static void Bind_Dropdownlist(string sql,DropDownList ddl,string value,string textvalue)
{
ddl.DataSource = getDataTable(sql);
ddl.DataTextField = textvalue;
ddl.DataValueField = value;
ddl.DataBind();
}
#endregion #region 为Repeater绑定数据
public static void Bind_Repeater(string sql, Repeater rpt,SqlConnection cn)
{
SqlDataReader dr = getDataReader(sql,cn);
rpt.DataSource = dr;
rpt.DataBind();
dr.Close();
dr.Dispose();
}
#endregion
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data.SqlClient;
/// <summary>
/// DB 的摘要说明
/// </summary>
public class DB
{
#region 公共成员
public static SqlConnection sqlconnection;//定义公共成员
public static readonly string cnstr = "server=fuck\\myds;database=zd_national;User Id=sa;pwd=123456;pooling=false";//数据库连接字符串
public static readonly string cnstr2 = "server=fuck\\myds;database=master;User Id=sa;pwd=123456;pooling=false";//数据库连接字符串
#endregion public DB()
{
//
// TODO: 在此处添加构造函数逻辑
//
} #region 打开数据库连接
/// <summary>
/// 打开数据库连接 返回SqlConnection
/// </summary>
/// <returns></returns>
public static SqlConnection OpenConnection()
{
try
{
sqlconnection = new SqlConnection(cnstr);
sqlconnection.Open();
return sqlconnection;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion #region 关闭数据库连接并释放资源
/// <summary>
/// 关闭数据库连接释放资源
/// </summary>
/// <param name="Conn">数据库连接对象</param>
public static void DisposeConnection(SqlConnection Conn)
{
if (Conn != null)
{
Conn.Close();
Conn.Dispose();
}
}
#endregion #region 执行Sql查询语句
///<summary>
/// 执行Sql查询语句 执行成功返回受影响的行数,执行失败返回-1;
/// </summary>
/// <param name="strSQL">传入的Sql语句</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteSql(string strSQL)
{
SqlConnection conn = OpenConnection();
try
{
SqlCommand comm = new SqlCommand(strSQL, conn);
int val = comm.ExecuteNonQuery(); DisposeConnection(conn);
return val; }
catch (Exception e)
{
DisposeConnection(conn);
throw new Exception(e.Message);
}
}
#endregion #region 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
/// <summary>
/// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
/// </summary>
/// <param name="strSQL">传入的Sql语句</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader getDataReader(string strSQL,SqlConnection cn)
{
SqlDataReader dr = null;
try
{ SqlCommand comm = new SqlCommand(strSQL, cn);
dr = comm.ExecuteReader();
return dr;
}
catch (Exception ex)
{
if (dr != null && !dr.IsClosed)
dr.Close();
DisposeConnection(cn);
throw new Exception(ex.Message);
} }
#endregion #region 返回指定Sql语句的DataTable
/// <summary>
/// 返回指定Sql语句的DataTable
/// </summary>
/// <param name="strSQL">传入的Sql语句</param>
/// <returns>DataTable</returns>
public static DataTable getDataTable(string strSQL)
{
SqlConnection conn = OpenConnection();
try
{
SqlCommand comm = new SqlCommand(strSQL, conn);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataTable table = new DataTable();
da.Fill(table); DisposeConnection(conn);
return table;
}
catch (Exception ex)
{
DisposeConnection(conn);
throw new Exception(ex.Message);
}
}
#endregion #region 返回指定Sql语句的DataSet
/// <summary>
/// 返回指定Sql语句的DataSet
/// </summary>
/// <param name="strSQL">传入的Sql语句</param>
/// <returns>DataSet</returns>
public static DataSet getDataSet(string strSQL)
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlConnection conn = OpenConnection();
try
{
SqlCommand comm = new SqlCommand(strSQL, conn);
comm.CommandType = CommandType.Text;
da.SelectCommand = comm;
da.Fill(ds); DisposeConnection(conn);
return ds;
}
catch (Exception e)
{
DisposeConnection(conn);
throw new Exception(e.Message);
}
}
#endregion #region 查询数据库中是否存在该条数据 存在返回True,不存在返回False
/// <summary>
/// 是否存在值
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static bool isExists(string strSQL)
{
SqlConnection conn = OpenConnection();
try
{
SqlCommand comm = new SqlCommand(strSQL,conn);
SqlDataReader dr = comm.ExecuteReader(); if (dr.HasRows) return true; DisposeConnection(conn);
return false;
}
catch (Exception ex)
{
DisposeConnection(conn);
throw new Exception(ex.Message);
} }
#endregion #region 获取记录总数
/// <summary>
/// 获取查询记录总数
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public static int getRowCount(string tableNm)
{
SqlConnection cn=OpenConnection();
int intRowCount = 0; string str = "select count(*) from (" + tableNm + ")";
SqlCommand cmd=new SqlCommand (str,cn);
intRowCount = (int)cmd.ExecuteScalar();
DisposeConnection(cn);
return intRowCount;
}
#endregion #region 为DropDownList绑定数据
public static void Bind_Dropdownlist(string sql,DropDownList ddl,string value,string textvalue)
{
ddl.DataSource = getDataTable(sql);
ddl.DataTextField = textvalue;
ddl.DataValueField = value;
ddl.DataBind();
}
#endregion #region 为Repeater绑定数据
public static void Bind_Repeater(string sql, Repeater rpt,SqlConnection cn)
{
SqlDataReader dr = getDataReader(sql,cn);
rpt.DataSource = dr;
rpt.DataBind();
dr.Close();
dr.Dispose();
}
#endregion
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货