Silverlight通过Webservice连接数据库 *** 作
silverlight（简称SL）进行数据库 *** 作，有多种方法，这里介绍最传统的通过WebService（简称WS）实现方式。本文的主要目的是在SL不支持DataSet和Datatable的基础上，提供通用的返回数据的WS方法。 一：创建项目 首先，创建SL应用程序，如QuestionsDbSL，创建的时候选择生成网站QuestionsDbSL.Web。另外，往往大家不想将SQL语句写在主应用程序中，所以，这里还需要创建一个业务层QuestionsDbSLServices。同时，实体层，我们创建为QuestionsDbSLModel。 QuestionsDbSL：SL主应用程序； QuestionsDbSL.Web：承载SL主应用程序的网站，同时也提供WS； QuestionsDbSLServices：SL类库项目，提供逻辑层；
QuestionsDbSLModel：SL类库项目，提供实体层，对应数据库表。 二：必要的辅助类和文件 第一个辅助类，就是sqlHelper，对数据库的直接 *** 作，由它提供直接对数据库的 *** 作。见附件一：sqlHelper.cs。该文件创建在QuestionsDbSL.Web.ClIEntBin中。 第二个是配置文件Web.config，这里需要用到的是定义数据连接字符串： <ppSettings>
<add key="ConnString" value="server =192.168.0.96; user ID = sa; password = sa; database = xxxProp"/>
</appSettings>
三：WS函数 首先是获取记录集的函数： public List<BaseVars> GetListBaseVars(string sql,params sqlParameter[] commandParams) 很遗憾的一点是，SL不支持DataSet或者Datatable，所以你无法直接返回一个记录集。但你可以通过返回一个List的方法来迂回解决。 如数据库表Student，则你可以返回一个List<Student>。问题的关键是，这样一来，你不得不为每一类查询提供一个WS函数。也许你会尝试用List<T>来解决这个问题，同样遗憾的是，SL调用WS函数的时候，只能序列化一般类型的类，所以我们必须换一个方式解决该问题。 要知道，数据表也就是各种基础类型的字段所组成的，所以，我们来模拟一张表就是： /// <summary>
/// 模拟数据库表
/// PS：一张表的字段不外乎几个基本数据类型，查询出来的值，在这里进行赋值
/// 本类的一个实例，相当于是一条记录，本类的一个实例数组，就相当于是datatable
/// 在UI端，你可将本类的实例数组转换为数据源
/// </summary>
public class BaseVars
{
public BaseVars()
{
ListString = new List<string>();
ListInt = new List<int>();
ListBool = new List<bool>();
ListByte = new List<byte>();
Listfloat = new List<float>();
ListDouble = new List<double>();
}
public List<string> ListString { get; set; }
public List<int> ListInt { get; set; }
public List<bool> ListBool { get; set; }
public List<byte> ListByte { get; set; }
public List<float> Listfloat { get; set; }
public List<double> ListDouble { get; set; }
} 则，WS函数就是如下： /// <summary>
/// 通用查询方法
/// 1：查询返回的记录，将会生成实例BaseVars；
/// 2：本方法返回BaseVars的列表；
/// 3：在调用方，要重新对数据进行组织，以便进行展示；
/// </summary>
/// <param name="sql"></param>
/// <param name="commandParams"></param>
/// <returns></returns>
[WebMethod]
public List<BaseVars> GetListBaseVars(string sql,params sqlParameter[] commandParams)
{
List<BaseVars> lr = new List<BaseVars>();
using (DataSet ds = sqlHelper.ExecuteDataSet(sql,commandParams))
{
if (ds == null || ds.tables[0].Rows.Count < 0)
{
return null;
}
lr = ListBaseDataSet.DataSetToListBaseVars(ds,0);
}
return lr;
} 注意到上段代码中，ListBaseDataSet.DataSetToListBaseVars(ds,0)就是将一个DataSet转换为BaseVars的一个实例列表。ListBaseDataSet.cs见附件二。 四：WS调用方 WS的调用在SL都是异步调用，WS提供的函数名在调用方都会被自动加上一个后缀Async。所以，如果你觉得有必要，你就需要提供一个委托函数来在WS调用完毕的做点什么，如下： wss.GetListBaseVarsCompleted += new EventHandler<QuestionsDbSLServices.ServiceReferenceYi.GetListBaseVarsCompletedEventArgs>(wss_GetListBaseVarsCompleted); wss是WS的实例，wss_GetListBaseVarsCompleted就是该委托函数。在该委托函数中，我是将得到的数据记录重构成一个具体对象的列表。
voID wss_GetListBaseVarsCompleted(object sender,QuestionsDbSLServices.ServiceReferenceYi.GetListBaseVarsCompletedEventArgs e)
{
ServiceReferenceYi.BaseVars[] lr = e.Result;
foreach (var item in lr)
{
QuestionsDbSLModel.Res_Source ds = new QuestionsDbSLModel.Res_Source();
ds.sourceID = item.ListInt[0];
ds.sourcename = item.ListString[0];
ds.dispOrder = item.ListInt[1];
ldResource.Add(ds);
} } 五：一个BUG 在业务层QuestionsDbSLServices引用WS后，在QuestionsDbSL使用QuestionsDbSLServices后会报错，查询结果是配置文件ServiceReferences.ClIEntConfig未被包含在XAP中。如果你直接在WEB中调用WS，则在打包项目的时候，ServiceReferences.ClIEntConfig被自动打包进去。 解决的办法是，你在WEB中，需要手动创建一个ServiceReferences.ClIEntConfig，然后将QuestionsDbSLServices的该文件的内容copY进去。 附件一：sqlHelper.cs
using System;
using System.Collections.Generic;
using System.linq;
using System.Web;
using System.Data.sqlClIEnt;
using System.Configuration;
using System.Data;
using System.Xml;
using System.Diagnostics;
namespace QuestionsDbSL.Web.ClIEntBin
{
[DeBUGgerStepThrough]
public sealed class sqlHelper
{
#region private utility methods & constructors
//Since this class provIDes only static methods,make the default constructor private to prevent
//instances from being created with "new sqlHelper()".
private sqlHelper() { }
/// <summary>
/// This method is used to attach array of sqlParameters to a sqlCommand.
/// This method will assign a value of dbnull to any parameter with a direction of
/// inputOutput and a value of null.
/// This behavior will prevent default values from being used,but
/// this will be the less common case than an intended pure output parameter (derived as inputOutput)
/// where the user provIDed no input value.
/// </summary>
/// <param name="command">The command to which the parameters will be added</param>
/// <param name="commandParameters">an array of sqlParameters tho be added to command</param>
private static voID AttachParameters(sqlCommand command,sqlParameter[] commandParameters)
{
foreach (sqlParameter p in commandParameters)
{
//check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.inputOutput) && (p.Value == null))
{
p.Value = dbnull.Value;
}
command.Parameters.Add(p);
}
}
/// <summary>
/// This method assigns an array of values to an array of sqlParameters.
/// </summary>
/// <param name="commandParameters">array of sqlParameters to be assigned values</param>
/// <param name="parameterValues">array of Components holding the values to be assigned</param>
private static voID AssignParameterValues(sqlParameter[] commandParameters,object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
//do nothing if we get no data
return;
}
// we must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
//iterate through the sqlParameters,assigning the values from the corresponding position in the
//value array
for (int i = 0,j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
}
/// <summary>
/// This method opens (if necessary) and assigns a connection,transaction,command type and parameters
/// to the provIDed command.
/// </summary>
/// <param name="command">the sqlCommand to be prepared</param>
/// <param name="connection">a valID sqlConnection,on which to execute this command</param>
/// <param name="transaction">a valID sqlTransaction,or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure,text,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParameters to be associated with the command or 'null' if no parameters are required</param>
private static voID PrepareCommand(sqlCommand command,sqlConnection connection,sqlTransaction transaction,CommandType commandType,string commandText,sqlParameter[] commandParameters)
{
//if the provIDed connection is not open,we will open it
if (connection.State != ConnectionState.Open)
connection.open();
//associate the connection with the command
command.Connection = connection;
command.CommandTimeout = 180;
//set the command text (stored procedure name or sql statement)
command.CommandText = commandText;
//if we were provIDed a transaction,assign it.
if (transaction != null)
command.Transaction = transaction;
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provIDed
if (commandParameters != null)
AttachParameters(command,commandParameters);
return;
}
#endregion private utility methods & constructors
#region DataHelpers
public static string CheckNull(object obj)
{
return (string)obj;
}
public static string CheckNull(dbnull obj)
{
return null;
}
#endregion
#region AddParameters
public static object CheckForNullString(string text)
{
if (text == null || text.Trim().Length == 0)
{
return dbnull.Value;
}
else
{
return text;
}
}
public static sqlParameter MakeInParam(string Paramname,object Value)
{
return new sqlParameter(Paramname,Value);
}
/// <summary>
/// Make input param.
/// </summary>
/// <param name="Paramname">name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public static sqlParameter MakeInParam(string Paramname,sqlDbType DbType,int Size,object Value)
{
return MakeParam(Paramname,DbType,Size,ParameterDirection.input,Value);
}
/// <summary>
/// Make input param.
/// </summary>
/// <param name="Paramname">name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <returns>New parameter.</returns>
public static sqlParameter MakeOutParam(string Paramname,int Size)
{
return MakeParam(Paramname,ParameterDirection.Output,null);
}
/// <summary>
/// Make stored procedure param.
/// </summary>
/// <param name="Paramname">name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Direction">Parm direction.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public static sqlParameter MakeParam(string Paramname,Int32 Size,ParameterDirection Direction,object Value)
{
sqlParameter param;
if (Size > 0)
param = new sqlParameter(Paramname,Size);
else
param = new sqlParameter(Paramname,DbType);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
#endregion
#region ExecuteNonquery
/// <summary>
/// 执行一个简单的sqlcommand 没有返回结果
/// </summary>
/// 例如:
/// int result = ExecuteNonquery("delete from test where 1>2 "); 返回 result =0
/// <param name="commandText">只能是SQL语句</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonquery(string commandText)
{
return ExecuteNonquery(ConfigurationManager.AppSettings["ConnString"],CommandType.Text,commandText,(sqlParameter[])null);
}
/// <summary>
/// 执行一个简单的sqlcommand 没有返回结果
/// </summary>
/// 例如:
/// int result = ExecuteNonquery("delete from test where tt =@tt",new sqlParameter("@tt",24));
/// <param name="commandText">只能是SQL语句</param>
/// <param name="commandParameters">参数</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonquery(string commandText,params sqlParameter[] commandParameters)
{
int i = 0;
using (sqlConnection cn = new sqlConnection(ConfigurationManager.AppSettings["ConnString"]))
{
cn.open();
return ExecuteNonquery(cn,commandParameters);
}
}
/// <summary>
/// Execute a sqlCommand (that returns no resultset and takes no parameters) against the database specifIEd in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonquery(connString,CommandType.StoredProcedure,"PublishOrders");
/// </remarks>
/// <param name="connectionString">a valID connection string for a sqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonquery(string connectionString,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteNonquery(connectionString,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns no resultset and takes no parameters) against the database specifIEd in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonquery(connString,commandType,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns no resultset) against the database specifIEd in the connection string
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonquery(connString,"PublishOrders",new sqlParameter("@prodID",24));
/// </remarks>
/// <param name="connectionString">a valID connection string for a sqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonquery(string connectionString,params sqlParameter[] commandParameters)
{
//create & open a sqlConnection,and dispose of it after we are done.
using (sqlConnection cn = new sqlConnection(connectionString))
{
cn.open();
//call the overload that takes a connection in place of the connection string
return ExecuteNonquery(cn,commandParameters);
}
}
/// <summary>
/// Execute a sqlCommand (that returns no resultset and takes no parameters) against the provIDed sqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonquery(conn,"PublishOrders");
/// </remarks>
/// <param name="connection">a valID sqlConnection</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonquery(sqlConnection connection,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteNonquery(connection,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns no resultset and takes no parameters) against the provIDed sqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonquery(conn,"PublishOrders");
/// </remarks>
/// <param name="connection">a valID sqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonquery(sqlConnection connection,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns no resultset) against the specifIEd sqlConnection
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonquery(conn,24));
/// </remarks>
/// <param name="connection">a valID sqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonquery(sqlConnection connection,params sqlParameter[] commandParameters)
{
return ExecuteNonquery(connection,commandParameters);
}
/// <summary>
/// Execute a sqlCommand (that returns no resultset) against the specifIEd sqlConnection
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonquery(conn,params sqlParameter[] commandParameters)
{
//create a command and prepare it for execution
sqlCommand cmd = new sqlCommand();
PrepareCommand(cmd,connection,(sqlTransaction)null,commandParameters);
//finally,execute the command.
int retval = cmd.ExecuteNonquery();
// detach the sqlParameters from the command object,so they can be used again.
cmd.Parameters.Clear();
if (connection.State == ConnectionState.Open)
connection.Close();
return retval;
}
/// <summary>
/// 执行一个简单的sqlcommand 没有返回结果
/// </summary>
/// 例如:
/// int result = ExecuteNonquery(myTran,"delete from test where 1>2 "); 返回 result =0
/// <param name="transaction">事务名称</param>
/// <param name="commandText">只能是SQL语句</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonquery(sqlTransaction transaction,string commandText)
{
return ExecuteNonquery(transaction,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns no resultset and takes no parameters) against the provIDed sqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonquery(trans,"PublishOrders");
/// </remarks>
/// <param name="transaction">a valID sqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonquery(sqlTransaction transaction,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteNonquery(transaction,(sqlParameter[])null);
}
/// <summary>
/// 带参数和参数的查询
/// </summary>
/// <param name="transaction"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static int ExecuteNonquery(sqlTransaction transaction,params sqlParameter[] commandParameters)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteNonquery(transaction,commandParameters);
}
/// <summary>
/// Execute a sqlCommand (that returns no resultset) against the specifIEd sqlTransaction
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonquery(trans,"Getorders",24));
/// </remarks>
/// <param name="transaction">a valID sqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonquery(sqlTransaction transaction,params sqlParameter[] commandParameters)
{
if (transaction == null)
{
//create & open a sqlConnection,and dispose of it after we are done.
using (sqlConnection cn = new sqlConnection(ConfigurationManager.AppSettings["ConnString"]))
{
cn.open();
//call the overload that takes a connection in place of the connection string
return ExecuteNonquery(cn,commandParameters);
}
}
else
{
//create a command and prepare it for execution
sqlCommand cmd = new sqlCommand();
PrepareCommand(cmd,transaction.Connection,commandParameters);
//finally,execute the command.
int retval = cmd.ExecuteNonquery();
// detach the sqlParameters from the command object,so they can be used again.
cmd.Parameters.Clear();
return retval;
}
}
#endregion ExecuteNonquery
#region 执行sqlDataAdapter
public static sqlDataAdapter ExecutesqlDataAdapter(string commandText)
{
using (sqlConnection cn = new sqlConnection(ConfigurationManager.AppSettings["ConnString"]))
{
cn.open();
return ExecutesqlDataAdapter(cn,commandText);
}
}
public static sqlDataAdapter ExecutesqlDataAdapter(sqlConnection connection,string commandText)
{
sqlDataAdapter myda = new sqlDataAdapter(commandText,connection);
myda.SelectCommand.CommandTimeout = 180;
connection.Close();
return myda;
}
#endregion
#region ExecuteDataSet
/// <summary>
/// 返回datataset 只需要传入查询语句
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteDataSet(ConfigurationManager.AppSettings["ConnString"],(sqlParameter[])null);
}
/// <summary>
/// 带参数查询
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandText,params sqlParameter[] commandParameters)
{
using (sqlConnection cn = new sqlConnection(ConfigurationManager.AppSettings["ConnString"]))
{
cn.open();
return ExecuteDataSet(cn,commandParameters);
}
}
/// <summary>
/// 执行存储过程 返回相应的dataset
/// </summary>
/// <param name="commandText">存储过程名字</param>
/// <param name="commandType"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandText,commandParameters);
}
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the database specifIEd in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(connString,"Getorders");
/// </remarks>
/// <param name="connectionString">a valID connection string for a sqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataSet(string connectionString,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteDataSet(connectionString,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the database specifIEd in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(connString,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the database specifIEd in the connection string
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(connString,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataSet(string connectionString,and dispose of it after we are done.
try
{
using (sqlConnection cn = new sqlConnection(connectionString))
{
cn.open();
//call the overload that takes a connection in place of the connection string
return ExecuteDataSet(cn,commandParameters);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 返回datataset 只需要传入查询语句
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(sqlConnection connection,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteDataSet(connection,commandText);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provIDed sqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(conn,"Getorders");
/// </remarks>
/// <param name="connection">a valID sqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataSet(sqlConnection connection,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the specifIEd sqlConnection
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(conn,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataSet(sqlConnection connection,commandParameters);
//create the DataAdapter & DataSet
sqlDataAdapter da = new sqlDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the DataSet using default values for Datatable names,etc.
da.Fill(ds);
// detach the sqlParameters from the command object,so they can be used again.
cmd.Parameters.Clear();
if (connection.State == ConnectionState.Open)
connection.Close();
//return the dataset
return ds;
}
/// <summary>
/// s事务中执行返回dataset
/// </summary>
/// <param name="transaction"></param>
/// <param name="commandText"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(sqlTransaction transaction,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteDataSet(transaction,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provIDed sqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans,"Getorders");
/// </remarks>
/// <param name="transaction">a valID sqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataSet(sqlTransaction transaction,(sqlParameter[])null);
}
/// <summary>
/// 事务中返回dataset 可带参数
/// </summary>
/// <param name="transaction"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(sqlTransaction transaction,params sqlParameter[] commandParameters)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteDataSet(transaction,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the specifIEd sqlTransaction
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataSet(sqlTransaction transaction,and dispose of it after we are done.
using (sqlConnection cn = new sqlConnection(ConfigurationManager.AppSettings["ConnString"]))
{
cn.open();
//call the overload that takes a connection in place of the connection string
return ExecuteDataSet(cn,commandParameters);
//create the DataAdapter & DataSet
sqlDataAdapter da = new sqlDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the DataSet using default values for Datatable names,etc.
da.Fill(ds);
// detach the sqlParameters from the command object,so they can be used again.
cmd.Parameters.Clear();
//return the dataset
return ds;
}
}
#endregion ExecuteDataSet
#region ExecuteDatatable
/// <summary>
/// 连接数据库
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
public static Datatable ExecuteDatatable(string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteDatatable(ConfigurationManager.AppSettings["ConnString"],(sqlParameter[])null);
}
/// <summary>
/// 连接数据库
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
public static Datatable ExecuteDatatable(string commandText,params sqlParameter[] commandParameters)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteDatatable(ConfigurationManager.AppSettings["ConnString"],commandParameters);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the database specifIEd in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// Datatable dt = ExecuteDatatable(connString,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>a Datatable containing the resultset generated by the command</returns>
public static Datatable ExecuteDatatable(string connectionString,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteDatatable(connectionString,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the database specifIEd in the connection string
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Datatable dt = ExecuteDatatable(connString,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>a Datatable containing the resultset generated by the command</returns>
public static Datatable ExecuteDatatable(string connectionString,and dispose of it after we are done.
using (sqlConnection cn = new sqlConnection(connectionString))
{
cn.open();
//call the overload that takes a connection in place of the connection string
return ExecuteDatatable(cn,commandParameters);
}
}
/// <summary>
/// 连接数据库
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
public static Datatable ExecuteDatatable(sqlConnection connection,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteDatatable(connection,commandText);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provIDed sqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// Datatable dt = ExecuteDatatable(conn,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>a Datatable containing the resultset generated by the command</returns>
public static Datatable ExecuteDatatable(sqlConnection connection,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the specifIEd sqlConnection
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Datatable dt = ExecuteDatatable(conn,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>a Datatable containing the resultset generated by the command</returns>
public static Datatable ExecuteDatatable(sqlConnection connection,commandParameters);
//create the DataAdapter & Datatable
sqlDataAdapter da = new sqlDataAdapter(cmd);
Datatable dt = new Datatable();
//fill the Datatable using default values for Datatable names,etc.
da.Fill(dt);
// detach the sqlParameters from the command object,so they can be used again.
cmd.Parameters.Clear();
if (connection.State == ConnectionState.Open)
connection.Close();
//return the Datatable
return dt;
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provIDed sqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// Datatable dt = ExecuteDatatable(trans,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>a Datatable containing the resultset generated by the command</returns>
public static Datatable ExecuteDatatable(sqlTransaction transaction,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteDatatable(transaction,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provIDed sqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// Datatable dt = ExecuteDatatable(trans,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the specifIEd sqlTransaction
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Datatable dt = ExecuteDatatable(trans,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>a Datatable containing the resultset generated by the command</returns>
public static Datatable ExecuteDatatable(sqlTransaction transaction,and dispose of it after we are done.
using (sqlConnection cn = new sqlConnection(ConfigurationManager.AppSettings["ConnString"]))
{
cn.open();
//call the overload that takes a connection in place of the connection string
return ExecuteDatatable(cn,commandParameters);
//create the DataAdapter & Datatable
sqlDataAdapter da = new sqlDataAdapter(cmd);
Datatable dt = new Datatable();
//fill the Datatable using default values for Datatable names,etc.
da.Fill(dt);
// detach the sqlParameters from the command object,so they can be used again.
cmd.Parameters.Clear();
//return the Datatable
return dt;
}
}
#endregion ExecuteDatatable
#region ExecuteReader
/// <summary>
/// this enum is used to indicate whether the connection was provIDed by the caller,or created by sqlHelper,so that
/// we can set the appropriate CommandBehavior when calling ExecuteReader()
/// </summary>
private enum sqlConnectioNownership
{
/// <summary>Connection is owned and managed by sqlHelper</summary>
Internal,
/// <summary>Connection is owned and managed by the caller</summary>
External
}
/// <summary>
/// 返回sqlDataReader 只是传入一条SQL语句
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns></returns>
public static sqlDataReader ExecuteReader(string commandText)
{
return ExecuteReader(ConfigurationManager.AppSettings["ConnString"],(sqlParameter[])null);
}
/// <summary>
/// 返回sqlDataReader 只是传入一条SQL语句和相应的参数
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static sqlDataReader ExecuteReader(string commandText,params sqlParameter[] commandParameters)
{
return ExecuteReader(ConfigurationManager.AppSettings["ConnString"],commandParameters);
}
/// <summary>
/// 返回sqlDataReader 只是传入一条SQL语句和相应的参数
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static sqlDataReader ExecuteReader(string commandText,commandParameters);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the database specifIEd in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// sqlDataReader dr = ExecuteReader(connString,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>a sqlDataReader containing the resultset generated by the command</returns>
public static sqlDataReader ExecuteReader(string connectionString,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteReader(connectionString,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the database specifIEd in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// sqlDataReader dr = ExecuteReader(connString,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the database specifIEd in the connection string
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// sqlDataReader dr = ExecuteReader(connString,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>a sqlDataReader containing the resultset generated by the command</returns>
public static sqlDataReader ExecuteReader(string connectionString,params sqlParameter[] commandParameters)
{
//create & open a sqlConnection
sqlConnection cn = new sqlConnection(connectionString);
cn.open();
try
{
//call the private overload that takes an internally owned connection in place of the connection string
return ExecuteReader(cn,null,commandParameters,sqlConnectioNownership.Internal);
}
catch
{
//if we fail to return the sqlDatReader,we need to close the connection ourselves
cn.Close();
throw;
}
}
/// <summary>
/// 返回sqlDataReader 只是传入一条SQL语句
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns></returns>
public static sqlDataReader ExecuteReader(sqlConnection connection,string commandText)
{
return ExecuteReader(connection,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provIDed sqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// sqlDataReader dr = ExecuteReader(conn,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>a sqlDataReader containing the resultset generated by the command</returns>
public static sqlDataReader ExecuteReader(sqlConnection connection,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteReader(connection,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the specifIEd sqlConnection
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// sqlDataReader dr = ExecuteReader(conn,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>a sqlDataReader containing the resultset generated by the command</returns>
public static sqlDataReader ExecuteReader(sqlConnection connection,params sqlParameter[] commandParameters)
{
//pass through the call to the private overload using a null transaction value and an externally owned connection
return ExecuteReader(connection,sqlConnectioNownership.External);
}
/// <summary>
/// Create and prepare a sqlCommand,and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection,we want the connection to be closed when the DataReader is closed.
///
/// If the caller provIDed the connection,we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">a valID sqlConnection,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectioNownership">indicates whether the connection parameter was provIDed by the caller,or created by sqlHelper</param>
/// <returns>sqlDataReader containing the results of the command</returns>
private static sqlDataReader ExecuteReader(sqlConnection connection,sqlParameter[] commandParameters,sqlConnectioNownership connectioNownership)
{
//create a command and prepare it for execution
sqlCommand cmd = new sqlCommand();
PrepareCommand(cmd,commandParameters);
//create a reader
sqlDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
if (connectioNownership == sqlConnectioNownership.External)
dr = cmd.ExecuteReader();
else
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// detach the sqlParameters from the command object,so they can be used again.
cmd.Parameters.Clear();
return dr;
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provIDed sqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// sqlDataReader dr = ExecuteReader(trans,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>a sqlDataReader containing the resultset generated by the command</returns>
public static sqlDataReader ExecuteReader(sqlTransaction transaction,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteReader(transaction,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the specifIEd sqlTransaction
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// sqlDataReader dr = ExecuteReader(trans,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>a sqlDataReader containing the resultset generated by the command</returns>
public static sqlDataReader ExecuteReader(sqlTransaction transaction,params sqlParameter[] commandParameters)
{
if (transaction == null)
{
//create & open a sqlConnection
sqlConnection cn = new sqlConnection(ConfigurationManager.AppSettings["ConnString"]);
cn.open();
try
{
//call the private overload that takes an internally owned connection in place of the connection string
return ExecuteReader(cn,sqlConnectioNownership.Internal);
}
catch
{
//if we fail to return the sqlDatReader,we need to close the connection ourselves
cn.Close();
throw;
}
}
else
//pass through to private overload,indicating that the connection is owned by the caller
return ExecuteReader(transaction.Connection,sqlConnectioNownership.External);
}
#endregion ExecuteReader
#region ExecuteScalar
/// <summary>
/// 返回ExecuteScalar 只是传入一条SQL语句
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns></returns>
public static object ExecuteScalar(string commandText)
{
return ExecuteScalar(ConfigurationManager.AppSettings["ConnString"],(sqlParameter[])null);
}
public static object ExecuteScalar(string commandText,params sqlParameter[] commandParameters)
{
return ExecuteScalar(ConfigurationManager.AppSettings["ConnString"],commandParameters);
}
/// <summary>
/// Execute a sqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specifIEd in
/// the connection string.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString,"GetorderCount");
/// </remarks>
/// <param name="connectionString">a valID connection string for a sqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(string connectionString,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteScalar(connectionString,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a 1x1 resultset) against the database specifIEd in the connection string
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString,"GetorderCount",etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(string connectionString,and dispose of it after we are done.
using (sqlConnection cn = new sqlConnection(connectionString))
{
cn.open();
//call the overload that takes a connection in place of the connection string
return ExecuteScalar(cn,commandParameters);
}
}
/// <summary>
/// 返回ExecuteScalar 只是传入一条SQL语句
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns></returns>
public static object ExecuteScalar(sqlConnection connection,string commandText)
{
return ExecuteScalar(connection,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a 1x1 resultset and takes no parameters) against the provIDed sqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn,"GetorderCount");
/// </remarks>
/// <param name="connection">a valID sqlConnection</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(sqlConnection connection,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteScalar(connection,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a 1x1 resultset) against the specifIEd sqlConnection
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(sqlConnection connection,params sqlParameter[] commandParameters)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteScalar(connection,commandParameters);
}
/// <summary>
/// Execute a sqlCommand (that returns a 1x1 resultset) against the specifIEd sqlConnection
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn,commandParameters);
//execute the command & return the results
object retval = cmd.ExecuteScalar();
// detach the sqlParameters from the command object,so they can be used again.
cmd.Parameters.Clear();
if (connection.State == ConnectionState.Open)
connection.Close();
return retval;
}
/// <summary>
/// Execute a sqlCommand (that returns a 1x1 resultset and takes no parameters) against the provIDed sqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans,"GetorderCount");
/// </remarks>
/// <param name="transaction">a valID sqlTransaction</param>
/// <param name="commandType">the CommandType (stored procedure,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(sqlTransaction transaction,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteScalar(transaction,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a 1x1 resultset and takes no parameters) against the provIDed sqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a 1x1 resultset) against the specifIEd sqlTransaction
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>an object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(sqlTransaction transaction,params sqlParameter[] commandParameters)
{
return ExecuteScalar(transaction,commandParameters);
}
/// <summary>
/// Execute a sqlCommand (that returns a 1x1 resultset) against the specifIEd sqlTransaction
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans,and dispose of it after we are done.
using (sqlConnection cn = new sqlConnection(ConfigurationManager.AppSettings["ConnString"]))
{
cn.open();
//call the overload that takes a connection in place of the connection string
return ExecuteScalar(cn,commandParameters);
//execute the command & return the results
object retval = cmd.ExecuteScalar();
// detach the sqlParameters from the command object,so they can be used again.
cmd.Parameters.Clear();
return retval;
}
}
#endregion ExecuteScalar
#region ExecuteXmlReader
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provIDed sqlConnection.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(conn,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command using "FOR XML auto"</param>
/// <returns>an XmlReader containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(sqlConnection connection,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteXmlReader(connection,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the specifIEd sqlConnection
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(conn,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command using "FOR XML auto"</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>an XmlReader containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(sqlConnection connection,commandParameters);
//create the DataAdapter & DataSet
XmlReader retval = cmd.ExecuteXmlReader();
// detach the sqlParameters from the command object,so they can be used again.
cmd.Parameters.Clear();
if (connection.State == ConnectionState.Open)
connection.Close();
return retval;
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset and takes no parameters) against the provIDed sqlTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command using "FOR XML auto"</param>
/// <returns>an XmlReader containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(sqlTransaction transaction,string commandText)
{
//pass through the call provIDing null for the set of sqlParameters
return ExecuteXmlReader(transaction,(sqlParameter[])null);
}
/// <summary>
/// Execute a sqlCommand (that returns a resultset) against the specifIEd sqlTransaction
/// using the provIDed parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans,etc.)</param>
/// <param name="commandText">the stored procedure name or T-sql command using "FOR XML auto"</param>
/// <param name="commandParameters">an array of sqlParamters used to execute the command</param>
/// <returns>an XmlReader containing the resultset generated by the command</returns>
public static XmlReader ExecuteXmlReader(sqlTransaction transaction,so they can be used again.
cmd.Parameters.Clear();
return retval;
}
#endregion ExecuteXmlReader
#region CreatesqlConnection
public static sqlConnection CreatesqlConnection(string connectionString)
{
//create & open a sqlConnection
sqlConnection cn = new sqlConnection(connectionString);
try
{
cn.open();
}
catch
{
//if we fail to return the sqlTransaction,we need to close the connection ourselves
cn.Close();
throw;
}
return cn;
}
public static sqlConnection CreatesqlConnection()
{
return CreatesqlConnection(ConfigurationManager.AppSettings["ConnString"]);
}
#endregion
#region ExecutesqlCommand
public static sqlCommand ExecutesqlCommand()
{
return ExecutesqlCommand("",ConfigurationManager.AppSettings["ConnString"]);
}
public static sqlCommand ExecutesqlCommand(string CmdText)
{
return ExecutesqlCommand(CmdText,ConfigurationManager.AppSettings["ConnString"]);
}
public static sqlCommand ExecutesqlCommand(sqlConnection cn)
{
if (cn.State == ConnectionState.Closed || cn.State == ConnectionState.broken)
cn.open();
try
{
return new sqlCommand("",cn);
}
catch
{
//if we fail to return the sqlCommand,we need to close the connection ourselves
cn.Close();
throw;
}
}
public static sqlCommand ExecutesqlCommand(string connectionString,string CmdText)
{
//create & open a sqlConnection
sqlConnection cn = new sqlConnection(connectionString);
cn.open();
try
{
return new sqlCommand(CmdText,we need to close the connection ourselves
cn.Close();
throw;
}
}
#endregion
}
}
附件二：ListBaseDataSet.cs
using System;
using System.Collections.Generic;
using System.linq;
using System.Web;
using System.Data;
using QuestionsDbSLModel;
namespace QuestionsDbSL.Web.ClIEntBin
{
/// <summary>
/// 用于进行通用数据转化的类
/// 将数据库中取出的数据通过 webserice传到SL端
/// </summary>
public class ListBaseDataSet
{
/// <summary>
/// 按列的次序，将值依次转换到ListBaseVar中
/// 按记录条数，将记录压到列表中
/// </summary>
/// <param name="p_DataSet"></param>
/// <param name="p_tableIndex"></param>
/// <returns></returns>
public static List<BaseVars> DataSetToListBaseVars(DataSet p_DataSet,int p_tableIndex)
{
if (p_DataSet == null || p_DataSet.tables.Count < 0)
return null;
if (p_tableIndex > p_DataSet.tables.Count - 1)
return null;
if (p_tableIndex < 0)
p_tableIndex = 0;
Datatable p_Data = p_DataSet.tables[p_tableIndex];
List<BaseVars> result = new List<BaseVars>();
lock (p_Data)
{
for (int j = 0; j < p_Data.Rows.Count; j++)
{
BaseVars lbv = new BaseVars();
for (int k = 0; k < p_Data.Columns.Count; k++)
{
if (p_Data.Rows[j][k].GetType() == typeof(string))
{
lbv.ListString.Add(p_Data.Rows[j][k].ToString());
}
else if (p_Data.Rows[j][k].GetType() == typeof(int))
{
lbv.ListInt.Add((int)(p_Data.Rows[j][k]));
}
else if (p_Data.Rows[j][k].GetType() == typeof(bool))
{
lbv.ListBool.Add((bool)(p_Data.Rows[j][k]));
}
else if (p_Data.Rows[j][k].GetType() == typeof(byte))
{
lbv.ListByte.Add((byte)(p_Data.Rows[j][k]));
}
else if (p_Data.Rows[j][k].GetType() == typeof(float))
{
lbv.Listfloat.Add((float)(p_Data.Rows[j][k]));
}
else if (p_Data.Rows[j][k].GetType() == typeof(double))
{
lbv.ListDouble.Add((double)(p_Data.Rows[j][k]));
}
}
result.Add(lbv); } } return result; } }}
总结以上是内存溢出为你收集整理的Silverlight通过Webservice连接数据库 *** 作全部内容，希望文章能够帮你解决Silverlight通过Webservice连接数据库 *** 作所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）