?? data.cs
字號:
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Reflection;
/// <summary>
//========高處不勝寒Asp.Net三層結構數據層公共類V1.1=========
//
// '''
// (0 0)
// +-----oOO----(_)-------------------+
// | |
// | 作者:高處不勝寒 |
// | QQ:28767360 |
// | AspXCn QQ群：14094415 |
// | 類型：Web版 |
// | 適用數據庫：Sql Sever |
// | 更新時間：2005-08－13 |
// | 技術支持網站:www.AspxCn.Org |
// | |
// +------------------oOO-------------+
// |__|__|
// || ||
// ooO Ooo
//
//============================================================
/// </summary>
namespace ThreeLayer.DAL
{
public abstract class Data
{
// ＝＝＝數據庫連接串設置＝＝＝
public static readonly string conn_Default = ConfigurationSettings.AppSettings["conn_Default"]; // 系統默認數據庫連接串
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝數據庫底層操作＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
/// <summary>
/// 執行ExecuteNonQuery
/// </summary>
/// <param name="connString">數據庫連接</param>
/// <param name="cmdType">Sql語句類型</param>
/// <param name="cmdText">Sql語句</param>
/// <param name="cmdParms">Parm數組</param>
/// <returns>返回影響行數</returns>
public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
cmd.Connection=conn;
cmd.CommandText = cmdText;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
return val;
}
}
/// <summary>
/// 返回一個SqlParameter實例
/// </summary>
/// <param name="ParamName">字段名</param>
/// <param name="stype">字段類型</param>
/// <param name="size">范圍</param>
/// <param name="Value">賦值</param>
/// <returns>返回一個SqlParameter實例</returns>
public static SqlParameter MakeParam(string ParamName,System.Data.SqlDbType stype,int size,Object Value)
{
SqlParameter para=new SqlParameter(ParamName,Value);
para.SqlDbType=stype;
para.Size=size;
return para;
}
/// <summary>
/// 獲得SqlParameter實例
/// </summary>
/// <param name="ParamName">字段名</param>
/// <param name="Value">賦值</param>
/// <returns>返回一個SqlParameter實例</returns>
public static SqlParameter MakeParam(string ParamName,string Value)
{
return new SqlParameter(ParamName, Value);
}
/// <summary>
/// 獲得DateSet實例(獲得單頁記錄)
/// </summary>
/// <param name="int_PageSize">一頁顯示的記錄數</param>
/// <param name="int_CurrentPageIndex">當前頁碼</param>
/// <param name="connString">數據庫連接串</param>
/// <param name="cmdType">Sql語句類型</param>
/// <param name="cmdText">Sql語句</param>
/// <param name="cmdParms">Parm數組</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(int int_PageSize,int int_CurrentPageIndex,string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlConnection conn = new SqlConnection(connString);
try
{
conn.Open();
System.Data.SqlClient.SqlDataAdapter da=new SqlDataAdapter(cmdText,conn);
da.SelectCommand.CommandType=cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
da.SelectCommand.Parameters.Add(parm);
}
conn.Close();
DataSet ds=new DataSet();
if (int_PageSize==0 && int_CurrentPageIndex==0)
{
da.Fill(ds,"12news1234567890");
}
else
{
int int_Page=int_PageSize*(int_CurrentPageIndex-1);
if (int_Page<0)
{
int_Page=0;
}
da.Fill(ds,int_Page,int_PageSize,"12news1234567890");
}
return ds;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 獲得DateSet實例(獲得全部記錄)
/// </summary>
/// <param name="connString">數據庫連接串</param>
/// <param name="cmdType">Sql語句類型</param>
/// <param name="cmdText">Sql語句</param>
/// <param name="cmdParms">Parm數組</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlConnection conn = new SqlConnection(connString);
try
{
conn.Open();
System.Data.SqlClient.SqlDataAdapter da=new SqlDataAdapter(cmdText,conn);
da.SelectCommand.CommandType=cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
da.SelectCommand.Parameters.Add(parm);
}
conn.Close();
DataSet ds=new DataSet();
da.Fill(ds,"12news1234567890");
return ds;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 執行ExecuteScalar
/// </summary>
/// <param name="connString">數據庫連接串</param>
/// <param name="cmdType">Sql語句類型</param>
/// <param name="cmdText">Sql語句</param>
/// <param name="cmdParms">Parm數組</param>
/// <returns>返回第一行第一列記錄值</returns>
public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
conn.Close();
return val;
}
}
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝數據庫操作：插入，修改，列表顯示，以及獲得詳細記錄＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
// ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝==
/// <summary>
/// 執行Sql語句
/// </summary>
/// <param name="connString">數據庫連接</param>
/// <param name="str_Sql">sql語句(比如：insert into tablename set name='北京'')</param>
public static void RunSql(string connString,string str_Sql)
{
Data.ExecuteNonQuery(connString,CommandType.Text,str_Sql);
}
/// <summary>
/// 插入記錄
/// </summary>
/// <param name="connString">數據庫連接</param>
/// <param name="cmdType">sql語句類型</param>
/// <param name="str_Sql">sql語句</param>
/// <param name="ht">表示層傳遞過來的哈希表對象</param>
public static void Insert(string connString,string TableName,Hashtable ht)
{
SqlParameter[] Parms=new SqlParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
int i=0;
// 作哈希表循環
while ( et.MoveNext() )
{
System.Data.SqlClient.SqlParameter sp=Data.MakeParam("@"+et.Key.ToString(),et.Value.ToString());
Parms[i]=sp; // 添加SqlParameter對象
i=i+1;
}
string str_Sql=GetInsertSqlbyHt(TableName,ht); // 獲得插入sql語句
Data.ExecuteNonQuery(connString,CommandType.Text,str_Sql,Parms);
}
/// <summary>
/// 刪除記錄
/// </summary>
/// <param name="connString">數據庫連接</param>
/// <param name="cmdType">sql語句類型</param>
/// <param name="str_Sql">sql語句</param>
/// <param name="ht">表示層傳遞過來的哈希表對象</param>
public static void Del(string connString,string TableName,string ht_Where,Hashtable ht)
{
SqlParameter[] Parms=new SqlParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
int i=0;
// 作哈希表循環
while ( et.MoveNext() )
{
System.Data.SqlClient.SqlParameter sp=Data.MakeParam("@"+et.Key.ToString(),et.Value.ToString());
Parms[i]=sp; // 添加SqlParameter對象
i=i+1;
}
string str_Sql=GetDelSqlbyHt(TableName,ht_Where,ht); // 獲得刪除sql語句
Data.ExecuteNonQuery(connString,CommandType.Text,str_Sql,Parms);
}
/// <summary>
/// 修改記錄
/// </summary>
/// <param name="connString">數據庫連接</param>
/// <param name="TableName">數據庫表名</param>
/// <param name="str_Where">傳遞條件,比如Id=@Id</param>
/// <param name="ht">表示層傳遞過來的哈希表對象</param>
public static void Update(string connString,string TableName,string ht_Where, Hashtable ht)
{
SqlParameter[] Parms=new SqlParameter[ht.Count];
IDictionaryEnumerator et = ht.GetEnumerator();
int i=0;
// 作哈希表循環
while ( et.MoveNext() )
{
System.Data.SqlClient.SqlParameter sp=Data.MakeParam("@"+et.Key.ToString(),et.Value.ToString());
Parms[i]=sp; // 添加SqlParameter對象
i=i+1;
}
string str_Sql=GetUpdateSqlbyHt(TableName,ht_Where,ht); // 獲得插入sql語句
Data.ExecuteNonQuery(connString,CommandType.Text,str_Sql,Parms);
}
/// <summary>
/// 獲得數字字段最大值(注：當該表記錄為空,返回0)
/// </summary>
/// <param name="connString">數據庫連接</param>
/// <param name="id">Key值字段名</param>
/// <param name="table_name">數據庫名</param>
/// <returns>返回數字字段最大值</returns>
public static int GetMaxId(string connString,string id,string table_name)
{
string str_Sql="Select Max("+id+") from "+table_name;
int int_MaxId=0;
object obj=Data.ExecuteScalar(connString,CommandType.Text,str_Sql,null);
if (obj==System.DBNull.Value)
{
int_MaxId=0;
}
else
{
int_MaxId = Convert.ToInt32(obj);
}
return int_MaxId;
}
/// <summary>
/// 通過傳遞條件獲得記錄條數
/// </summary>
/// <param name="ht">表示層傳遞過來的條件字段參數</param>
/// <returns>返回記錄條數</returns>
public static int GetRsCount(string connString,string Table,string ht_Where,Hashtable ht)
{
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -