利用数据集实现对数据库的操作
2024-07-21 02:22:51
供稿：网友
注册会员，创建你的web开发资料库，/// <summary>
/// 说明: 本类主要实现对数据库的操作(查询|sp)
/// 建立者: 黄宗银
/// 建立时间: 2004-12-4
/// </summary>
public class db
{
/// <summary>
/// 从数据库查询数据
/// </summary>
/// <param name="columns">查询列名</param>
/// <param name="target">查询目标</param>
/// <param name="pkcolumn">主键列名</param>
/// <param name="pkvalue">主键值</param>
/// <param name="q">是否加引号</param>
/// <param name="strex">异常信息</param>
/// <returns>返回查询结果</returns>
public static dataview getdbdata( string columns, string target, string pkcolumn, string pkvalue, bool q, ref string strex )
{
string strcondition = "[" + pkcolumn + "] = ";
if( q )
{
strcondition += "'" + pkvalue + "'";
}
else
{
strcondition += pkvalue;
}
dataset ds = getdbdata( columns, target, strcondition, ref strex );
if( strex != string.empty )
{
return null;
}
return ds.tables[0].defaultview;
}
/// <summary>
/// 从数据库查询数据
/// </summary>
/// <param name="columns">查询列名</param>
/// <param name="target">查询目标</param>
/// <param name="condition">查询条件</param>
/// <param name="strex">异常信息</param>
/// <returns>查询结果</returns>
public static dataset getdbdata( string columns, string target, string condition, ref string strex )
{
strex = string.empty;
string strconnect = configurationsettings.appsettings["connectstring"];
sqlconnection sqlconnection = new sqlconnection( strconnect );
try
{
string strquery = "select " + columns + "from [" + target + "]";
if( condition.trim() != string.empty )
{
strquery += "where [" + condition;
}
sqldataadapter sqldataadapter = new sqldataadapter( strquery, sqlconnection );
dataset ds = new dataset();
sqldataadapter.fill( ds );
sqlconnection.close();
return ds;
}
catch( sqlexception ex )
{
sqlconnection.close();
strex = ex.message;
return null;
}
}
/// <summary>
/// 执行某个存储过程
/// </summary>
/// <param name="sp">存储过程名</param>
/// <param name="xsd">数据集对象</param>
/// <param name="strex">异常信息</param>
/// <returns>执行结果</returns>
public static void execsp( string sp, dataset xsd, ref string strex )
{
string strconnect = configurationsettings.appsettings["connectstring"];
sqlconnection sqlconnection = new sqlconnection( strconnect );
try
{
sqldataadapter sqldataadapter = new sqldataadapter( sp, sqlconnection );
sqldataadapter.selectcommand.commandtype = commandtype.storedprocedure;
for( int i = 0; i < xsd.tables["in"].columns.count; i++ )
{
string strcolumnname = xsd.tables["in"].columns[i].columnname;
sqldataadapter.selectcommand.parameters.add( "@" + strcolumnname, (xsd.tables["in"].rows[0])[i] );
}
for( int j = 0; j < xsd.tables["out"].columns.count; j++ )
{
string strcolumnname = xsd.tables["out"].columns[j].columnname;
sqldataadapter.selectcommand.parameters.add( "@" + strcolumnname, xsd.tables["out"].columns[j].datatype );
sqldataadapter.selectcommand.parameters["@" + strcolumnname].direction = parameterdirection.output;
}
sqldataadapter.fill( xsd, "out" );
}
catch( sqlexception ex )
{
strex = ex.message;
}
finally
{
sqlconnection.close();
}
}
}
