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