asp.net sql 數(shù)據(jù)庫處理函數(shù)命令

更新時間：2009年10月29日 23:47:30 作者：

asp.net sql 數(shù)據(jù)庫處理函數(shù)命令 ,需要的朋友可以參考下。

先寫一個數(shù)據(jù)庫統(tǒng)計函數(shù)

 
public static int Count(string cityName) 
{ 
string cmdText = ""; 
SqlConnection conn = new SqlConnection(DBH.DBA); 
SqlCommand cmd = null; 
cmdText = "Select count(*) From [Drugstore] Where CityName=@cityName "; 
cmd = new SqlCommand(cmdText, conn); 
cmd.Parameters.AddWithValue("@cityName", cityName); 
conn.Open(); 
int total = (int)cmd.ExecuteScalar(); 
conn.Close(); 
return total; 
} 

剛開始全部函數(shù)調(diào)用是這樣一個個寫出來的，后來熟悉了用SqlHelp方便好多，在后來就直接用動軟.net代碼生成器了，發(fā)現(xiàn)對原先的這些越來越陌生了，現(xiàn)在大致整理一下，和上面重復的代碼部分省略。
1. 添加數(shù)據(jù)

復制代碼代碼如下:

 
DrugstoreInfo info = new DrugstoreInfo(); 
cmd.Parameters.AddWithValue("@ID",info.ID); 
try 
{ 
conn.Open(); 
return cmd.ExecuteNonQuery(); 
} 
catch 
{ 
throw; 
} 
finally 
{ 
conn.Close(); 
} 

.dataset 數(shù)據(jù)分頁

復制代碼代碼如下:

 
public static DataSet indexQuery(int pageIndex, int pageSize) 
{ 
.. 
SqlDataAdapter da = new SqlDataAdapter(cmd); 
DataSet ds = new DataSet(); 
da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "Table1"); 
return ds; 
} 

. 獲得最大值

復制代碼代碼如下:

 
public static int GetMax() 
{ 
string cmdText = "select Max(Id) from Application "; 
.. 
try 
{ 
conn.Open(); 
Object obj = cmd.ExecuteScalar(); 
if (obj == null || obj is DBNull) 
{ 
return 1; 
} 
return (int)obj + 1; 
} 
catch 
{ 
throw; 
} 
finally 
{ 
conn.Close(); 
} 
} 

. select 選擇

復制代碼代碼如下:

 
public static ApplicationInfo Select(int id) 
{ 
string cmdText = "select ID from Application where ID=@ID"; 
SqlConnection conn = new SqlConnection(DBH.ConnString); 
SqlCommand cmd = new SqlCommand(cmdText, conn); 
ApplicationInfo info = new ApplicationInfo(); 
cmd.Parameters.AddWithValue("@ID", id); 
conn.Open(); 
using (IDataReader dr = cmd.ExecuteReader()) 
{ 
if (dr.Read()) 
{ 
info.ID = (int)dr["ID"]; 
} 
dr.Close(); 
} 
conn.Close(); 
return info; 
} 

.delete 刪除

復制代碼代碼如下:

 
public static int Del(int id) 
{ 
string cmdText = "Delete from Application Where ID= @ID"; 
SqlConnection conn = new SqlConnection(DBH.ConnString); 
SqlCommand cmd = new SqlCommand(cmdText, conn); 
cmd.Parameters.AddWithValue("@ID", id); 
conn.Open(); 
return cmd.ExecuteNonQuery(); 
conn.Close(); 
} 

. update 修改

復制代碼代碼如下:

 
public static int Update(ApplicationInfo info) 
{ 
string cmdText = "Update Application Set City=@city Where ID=@ID"; 
SqlConnection conn = new SqlConnection(DBH.ConnString); 
SqlCommand cmd = new SqlCommand(cmdText, conn); 
cmd.Parameters.AddWithValue("@ID", info.ID); 
try 
{ 
conn.Open(); 
return cmd.ExecuteNonQuery(); 
} 
catch 
{ 
throw; 
} 
finally 
{ 
conn.Close(); 
} 
} 

.配置

復制代碼代碼如下:

 
public class DBH 
{ 
private DBH() { } 
private static readonly string _DBA = ConfigurationManager.ConnectionStrings["DBA"].ConnectionString; 
public static string DBA 
{ 
get { return _DBA; } 
} 
} 

附: configurationManager 需要命名空間 using System.Configuration 和添加引用System.Configuration 雙重操作。