asp.net sql 数据库处理函数命令

先写一个数据库统计函数

代码如下：

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; 
}

刚开始全部函数调用是这样一个个写出来的，后来熟悉了 用SqlHelp方便好多，在后来就直接用动软.net代码生成器了，发现对原先的这些越来越陌生了 ，现在大致整理一下，和上面重复的代码部分省略。
1. 添加数据

代码如下：

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

.dataset 数据分页

代码如下：

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 双重操作。