using System; using System.ComponentModel; using System.Collections; using System.Diagnostics; using System.Data; using System.Data.SqlClient; using System.Configuration;namespace PetShop.Components { /// <summary> /// ADO.NET data access using the SQL Server Managed Provider. /// </summary> public class Database : IDisposable { // connection to data source private SqlConnection con;
/// <summary> /// Run stored procedure. /// </summary> /// <param name="procName">Name of stored procedure.</param> /// <returns>Stored procedure return value.</returns> public int RunProc(string procName) { SqlCommand cmd = CreateCommand(procName, null); cmd.ExecuteNonQuery(); this.Close(); return (int)cmd.Parameters["ReturnValue"].Value; } /// <summary> /// Run stored procedure. /// </summary> /// <param name="procName">Name of stored procedure.</param> /// <param name="prams">Stored procedure params.</param> /// <returns>Stored procedure return value.</returns> public int RunProc(string procName, SqlParameter[] prams) { SqlCommand cmd = CreateCommand(procName, prams); cmd.ExecuteNonQuery(); this.Close(); return (int)cmd.Parameters["ReturnValue"].Value; } /// <summary> /// Run stored procedure. /// </summary> /// <param name="procName">Name of stored procedure.</param> /// <param name="dataReader">Return result of procedure.</param> public void RunProc(string procName, out SqlDataReader dataReader) { SqlCommand cmd = CreateCommand(procName, null); dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } /// <summary> /// Run stored procedure. /// </summary> /// <param name="procName">Name of stored procedure.</param> /// <param name="prams">Stored procedure params.</param> /// <param name="dataReader">Return result of procedure.</param> public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader) { SqlCommand cmd = CreateCommand(procName, prams); dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); }
/// <summary> /// Create command object used to call stored procedure. /// </summary> /// <param name="procName">Name of stored procedure.</param> /// <param name="prams">Params to stored procedure.</param> /// <returns>Command object.</returns> private SqlCommand CreateCommand(string procName, SqlParameter[] prams) { // make sure connection is open Open(); //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) ); SqlCommand cmd = new SqlCommand(procName, con); cmd.CommandType = CommandType.StoredProcedure; // add proc parameters if (prams != null) { foreach (SqlParameter parameter in prams) cmd.Parameters.Add(parameter); }
// return param cmd.Parameters.Add( new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return cmd; } /// <summary> /// Open the connection. /// </summary> private void Open() { // open connection if (con == null) { con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); con.Open(); } } /// <summary> /// Close the connection. /// </summary> public void Close() { if (con != null) con.Close(); } /// <summary> /// Release resources. /// </summary> public void Dispose() { // make sure connection is closed if (con != null) { con.Dispose(); con = null; } } /// <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 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 SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, 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 SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) { SqlParameter param; if(Size > 0) param = new SqlParameter(ParamName, DbType, Size); else param = new SqlParameter(ParamName, DbType); param.Direction = Direction; if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value; return param; } } }
用ms enterprise library http://msdn.microsoft.com/practices/guidetype/AppBlocks/default.aspx?pull=/library/en-us/dnpag2/html/entlib.asp 按照说明看一下先 然后下在,生成有一个Microsoft.ApplicationBlocks.Data.dll 添加这个.dll到lz的引用中 在代码中导入 Microsoft.ApplicationBlocks.Data这个命名空间里面有一个sqlhelper类 那就是lz想要的了 关于sqlhelper class的使用,可以参考ms Microsoft patterns & practices 我这里举一个例子 Imports Microsoft.ApplicationBlocks.Data Imports System.Data.SqlClient Imports System.Data Namespace hudongli.lostfound.Data Public Class SqlDataProvider '定义_ConnectionString,得到连接字符串 Private _ConnectionString As SqlConnection Public Sub New() _ConnectionString = hudongli.lostfound.Common.config.GetConnectionString()
End Sub Public ReadOnly Property ConnectionString() As String Get Return _ConnectionString.ConnectionString End Get End Property ' general Private Function GetNull(ByVal Field As Object) As Object Return hudongli.lostfound.Common.Null.GetNull(Field, DBNull.Value) End Function ' tab Public Function AddTab(ByVal PortalId As Integer, ByVal TabName As String, ByVal IsVisible As Boolean, ByVal DisableLink As Boolean, ByVal ParentId As Integer, ByVal IconFile As String, ByVal Title As String, ByVal Description As String, ByVal KeyWords As String, ByVal Url As String, ByVal SkinSrc As String, ByVal ContainerSrc As String, ByVal TabPath As String, ByVal StartDate As Date, ByVal EndDate As Date, ByVal RefreshInterval As Integer, ByVal PageHeadText As String) As Integer Return CType(SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.StoredProcedure & "AddTab", GetNull(PortalId), TabName, IsVisible, DisableLink, GetNull(ParentId), IconFile, Title, Description, KeyWords, Url, GetNull(SkinSrc), GetNull(ContainerSrc), TabPath, GetNull(StartDate), GetNull(EndDate), GetNull(RefreshInterval), GetNull(PageHeadText)), Integer) End Function end class end namespace 没有写注释,lz自己try一下
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace PetShop.Components {
/// <summary>
/// ADO.NET data access using the SQL Server Managed Provider.
/// </summary>
public class Database : IDisposable {
// connection to data source
private SqlConnection con;
/// <summary>
/// Run stored procedure.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <returns>Stored procedure return value.</returns>
public int RunProc(string procName) {
SqlCommand cmd = CreateCommand(procName, null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
} /// <summary>
/// Run stored procedure.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="prams">Stored procedure params.</param>
/// <returns>Stored procedure return value.</returns>
public int RunProc(string procName, SqlParameter[] prams) {
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
} /// <summary>
/// Run stored procedure.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="dataReader">Return result of procedure.</param>
public void RunProc(string procName, out SqlDataReader dataReader) {
SqlCommand cmd = CreateCommand(procName, null);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
} /// <summary>
/// Run stored procedure.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="prams">Stored procedure params.</param>
/// <param name="dataReader">Return result of procedure.</param>
public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader) {
SqlCommand cmd = CreateCommand(procName, prams);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
/// <summary>
/// Create command object used to call stored procedure.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="prams">Params to stored procedure.</param>
/// <returns>Command object.</returns>
private SqlCommand CreateCommand(string procName, SqlParameter[] prams) {
// make sure connection is open
Open(); //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure; // add proc parameters
if (prams != null) {
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// return param
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null)); return cmd;
} /// <summary>
/// Open the connection.
/// </summary>
private void Open() {
// open connection
if (con == null) {
con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
con.Open();
}
} /// <summary>
/// Close the connection.
/// </summary>
public void Close() {
if (con != null)
con.Close();
} /// <summary>
/// Release resources.
/// </summary>
public void Dispose() {
// make sure connection is closed
if (con != null) {
con.Dispose();
con = null;
}
} /// <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 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 SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) {
return MakeParam(ParamName, DbType, Size, 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 SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) {
SqlParameter param; if(Size > 0)
param = new SqlParameter(ParamName, DbType, Size);
else
param = new SqlParameter(ParamName, DbType); param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
}
}
http://msdn.microsoft.com/practices/guidetype/AppBlocks/default.aspx?pull=/library/en-us/dnpag2/html/entlib.asp
按照说明看一下先
然后下在,生成有一个Microsoft.ApplicationBlocks.Data.dll
添加这个.dll到lz的引用中
在代码中导入
Microsoft.ApplicationBlocks.Data这个命名空间里面有一个sqlhelper类
那就是lz想要的了
关于sqlhelper class的使用,可以参考ms Microsoft patterns & practices
我这里举一个例子
Imports Microsoft.ApplicationBlocks.Data
Imports System.Data.SqlClient
Imports System.Data
Namespace hudongli.lostfound.Data
Public Class SqlDataProvider
'定义_ConnectionString,得到连接字符串
Private _ConnectionString As SqlConnection Public Sub New() _ConnectionString = hudongli.lostfound.Common.config.GetConnectionString()
End Sub
Public ReadOnly Property ConnectionString() As String
Get
Return _ConnectionString.ConnectionString
End Get
End Property
' general
Private Function GetNull(ByVal Field As Object) As Object
Return hudongli.lostfound.Common.Null.GetNull(Field, DBNull.Value)
End Function ' tab
Public Function AddTab(ByVal PortalId As Integer, ByVal TabName As String, ByVal IsVisible As Boolean, ByVal DisableLink As Boolean, ByVal ParentId As Integer, ByVal IconFile As String, ByVal Title As String, ByVal Description As String, ByVal KeyWords As String, ByVal Url As String, ByVal SkinSrc As String, ByVal ContainerSrc As String, ByVal TabPath As String, ByVal StartDate As Date, ByVal EndDate As Date, ByVal RefreshInterval As Integer, ByVal PageHeadText As String) As Integer
Return CType(SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.StoredProcedure & "AddTab", GetNull(PortalId), TabName, IsVisible, DisableLink, GetNull(ParentId), IconFile, Title, Description, KeyWords, Url, GetNull(SkinSrc), GetNull(ContainerSrc), TabPath, GetNull(StartDate), GetNull(EndDate), GetNull(RefreshInterval), GetNull(PageHeadText)), Integer)
End Function
end class
end namespace
没有写注释,lz自己try一下