我想做好一个数据访问类文件以便在以后使用，只要传给他一个SQL字符串便执行相应的代码。

[aspx_vb]

我想做好一个数据访问类文件以便在以后使用，只要传给他一个SQL字符串便执行相应的代码。

如果知道了数据表的字段名比较好做，问题是我写类的时候并不知道数据表的字段名//
如下：

类调用

Dim UPclass As New DATA_Class
UPclass.UpdateDATA("Update powers Set mf=@mf, tby=@tby, fj=@fj, bj=@bj, zs=@zs Where id=3"）
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DATA_Class.vb文件

Public Class DATA Class

Public Adpt As SqlDataAdapter

Public Conn As SqlConnection, Cmd As SqlCommand
Public SQL As String
Public DB = ConfigurationSettings.AppSettings("DB")
'--------------------------------------------------------------------------

Public Sub UpdateDATA(ByVal SqlName As String)
Conn = New SqlConnection( DB)
Conn.Open()

SQL = sqlName '传入的SQL命令
Cmd = New SqlCommand( SQL, Conn)

'------------这里边的怎么办,CheckBox11.Checked怎么传进来,Parameters.Add要怎么做---
' Cmd.Parameters.Add("@mf", CheckBox11.Checked)
' Cmd.Parameters.Add("@tby", CheckBox10.Checked)
' Cmd.Parameters.Add("@fj", CheckBox9.Checked)
' Cmd.Parameters.Add("@bj", CheckBox8.Checked)
' Cmd.Parameters.Add("@zs", CheckBox7.Checked)
'---------------------------------------------------------------------------------
Cmd.ExecuteNonQuery()
Conn.Close()
End Sub
End Class

我初步是想用正则表达把 SQL 字符串分割，如 Update powers Set mf=@mf, tby=@tby, fj=@fj, bj=@bj, zs=@zs Where id=3 再读出 @ 左右边的字符。不知大家有没有什么便好的办法?????

[fanweiwei]

不会帮你顶一下！

[xiaoniaofei]

可以用SqlHelper.cs，Microsoft的！！

[posemanboy]

所有的字段,表名以及条件 全部用变量,在存储过程里面传递进去.

[LoveCherry]

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace LCBBS.Provider
{
public class SqlDataProvider
{
public SqlDataProvider()
{

}
private string _strconnection = ConfigurationSettings.AppSettings["strconnection"];
private SqlConnection _connection;
private SqlDataAdapter _dataadapter;
private SqlCommand _command;
private SqlDataReader _datareader;

# region 获取连接字符串
public string ConnectionString
{
get {return this._strconnection;}
set {this._strconnection = value;}
}
# endregion

# region 执行语句
public void Execute(string strsql)
{
_connection=new SqlConnection(_strconnection);
_command=new SqlCommand(strsql,_connection);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
_command.Dispose();
}
#endregion

# region 填充数据集
public void FillDataSet(string strSql,string strDataTableName,ref DataSet ds)
{
_connection=new SqlConnection(_strconnection);
_dataadapter=new SqlDataAdapter(strSql,_connection);
_dataadapter.Fill(ds,strDataTableName);
_connection.Close();
_dataadapter.Dispose();

}
# endregion

# region 执行存储过程
public void NewSP(string spName)
{
_connection=new SqlConnection(_strconnection);
_command=new SqlCommand(spName,_connection);
_command.CommandType = CommandType.StoredProcedure;
}
public void AddInParm(string ver,SqlDbType type,int ilng,string s)
{
_command.Parameters.Add(ver,type,ilng);
_command.Parameters[ver].Value=s;
}
public void AddInParm(string ver,SqlDbType type,string s)
{
_command.Parameters.Add(ver,type);
_command.Parameters[ver].Value=s;
}
public void AddReturnParm(string ver,SqlDbType type,int ilng)
{
_command.Parameters.Add(ver,type,ilng);
_command.Parameters[ver].Direction=ParameterDirection.Output;
}
public void AddReturnParm(string ver,SqlDbType type)
{
_command.Parameters.Add(ver,type);
_command.Parameters[ver].Direction=ParameterDirection.ReturnValue;
}
public void ExecuteSP()
{
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
_command.Dispose();
}
public void ExecuteSP(string ver,ref string strout)
{
_connection.Open();
_command.ExecuteNonQuery();
strout=_command.Parameters[ver].Value.ToString();
_connection.Close();
_command.Dispose();
}
# endregion

}
}

[lyj670]

Dim _hz As Boolean = False

Public Property hz() As Boolean
Get
Return _hz
End Get
Set(ByVal Value As Boolean)
_hz = Value
End Set
End Property
这样就可以在调用的地方设置hz这个参数了。要几个参数都可以

[mac_shen]

用SqlHelper.cs吧，很简便

[aspx_vb]

我不想用存储过程呀。。。。

[evaELLIS]

这样作太麻烦了，但是不知道你用的这种方法是否会很快。
但我一般都是用SqlDataAdapter里的updata方法，直接将数据保存到数据库里。
它只需要一种select 语句就行了

[blackant2]

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace BAWebLib
{
/// <summary>
/// DataBase 的摘要说明。
/// </summary>
public abstract class Database
{
public static readonly string CONN_STRING = ConfigurationSettings.AppSettings["ConnString"];

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();

cmd.Connection = conn;
cmd.CommandText = cmdText;

if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}


}
}

public static System.Data.DataSet Fill(string cmdText)
{
try
{
return Fill(CommandType.Text,cmdText,null);
}
catch(Exception excep)
{
throw(excep);
}
}
public static System.Data.DataSet Fill(string cmdText,SqlParameter[] cmdParms)
{
try
{
return Fill(CommandType.Text,cmdText,cmdParms);
}
catch(Exception excep)
{
throw(excep);
}
}
public static System.Data.DataSet Fill(CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
try
{
System.Data.DataSet ds=new DataSet();
SqlConnection conn=new SqlConnection(CONN_STRING);
SqlCommand cmd=new SqlCommand();
SqlDataAdapter sda=new SqlDataAdapter(cmd);
PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms);
sda.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch(Exception excep)
{
throw(excep);
}
}
public static System.Data.SqlClient.SqlDataReader ExecuteReader(string cmdText)
{
try
{
return ExecuteReader(CommandType.Text,cmdText,null);
}
catch(Exception excep)
{
throw(excep);
}
}

public static System.Data.SqlClient.SqlDataReader ExecuteReader(string cmdText,SqlParameter[] param)
{
try
{
return ExecuteReader(CommandType.Text,cmdText,param);
}
catch(Exception excep)
{
throw(excep);
}
}
public static System.Data.SqlClient.SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
try
{
SqlConnection conn=new SqlConnection(CONN_STRING);
SqlCommand cmd=new SqlCommand();

PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms);

//throw(new Exception("cmd.Parameters[1].ParameterName:"+cmd.Parameters[1].ParameterName.ToString()+"cmd.Parameters[1].Value:"+cmd.Parameters[1].Value.ToString()));
SqlDataReader sdr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return sdr;

}
catch(Exception excep)
{
throw(excep);
}
}
public static int ExecuteNonQuery( string cmdText)
{
try
{
return ExecuteNonQuery(CommandType.Text,cmdText,null);
}
catch(Exception excep)
{
throw(excep);
}
}
public static int ExecuteNonQuery( string cmdText, SqlParameter[] cmdParms)
{
try
{
return ExecuteNonQuery(CommandType.Text ,cmdText,cmdParms);
}
catch(Exception excep)
{
throw(excep);
}
}

public static int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd=new SqlCommand();
SqlConnection conn=new SqlConnection(CONN_STRING);
PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms);
int returnValue=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return returnValue;
}
catch(Exception excep)
{
throw(excep);
}
}

public static object ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
try
{
SqlCommand cmd=new SqlCommand();
SqlConnection conn=new SqlConnection(CONN_STRING);
PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms);
object returnValue=cmd.ExecuteScalar();
cmd.Parameters.Clear();
return returnValue;
}
catch(Exception excep)
{
throw(excep);
}
}

private static SqlParameter MakeParam(string paramName,SqlDbType paramType,int paramSize,ParameterDirection paramDerection)
{
SqlParameter param=new SqlParameter(paramName,paramType);
if(paramSize>0){param.Size=paramSize;}
param.Direction=paramDerection;
return param;
}
public static SqlParameter MakeInParam(string paramName,SqlDbType paramType,int paramSize,object paramValue)
{
try
{
SqlParameter param=MakeParam(paramName,paramType,paramSize,ParameterDirection.Input);
param.Value=paramValue;
return param;
}
catch(Exception excep)
{
throw(excep);
}
}

public static SqlParameter MakeOutParam(string paramName,SqlDbType paramType,int paramSize)
{
try
{
return MakeParam(paramName,paramType,paramSize,ParameterDirection.Output);
}
catch(Exception excep)
{
throw(excep);
}
}
public static SqlParameter MakeReturnParam(string paramName)
{
try
{
return MakeParam(paramName,SqlDbType.Int,0,ParameterDirection.ReturnValue);
}
catch(Exception excep)
{
throw(excep);
}
}
}
}

[blackant2]

带参数调用的示例
tt88.User user=Main.getCurrentUser();
System.Text.StringBuilder sb=new System.Text.StringBuilder(" update leadbbs_announce set ");
sb.Append("Title=@Title");
sb.Append(",content=@content");
sb.Append(",printcontent=@printcontent");
sb.Append(",length=@length");
sb.Append(",faceicon=@faceicon");
sb.Append(",underwriteflag=@underwriteflag");
sb.Append(",notreplay=@notreplay");
sb.Append(",OtherInfo=LastInfo");
sb.Append(",LastInfo=@LastInfo");
sb.Append(" where BoardID=@BoardID");
sb.Append(" and [ID]=@AnnounceID");
String strLastInfo=String.Format(Main.getResource("ModifyInfo"),System.DateTime.Now.ToString(Main.getResource("LongDate")),user.UserName);
SqlParameter[] param=new SqlParameter[]{
BAWebLib.Database.MakeInParam("@Title",SqlDbType.NVarChar,255,strTitle),
BAWebLib.Database.MakeInParam("@content",SqlDbType.NText,0,Main.UBB2HTML(strContent)),
BAWebLib.Database.MakeInParam("@printcontent",SqlDbType.NText,0,Main.UBB2HTML(strContent)),
BAWebLib.Database.MakeInParam("@length",SqlDbType.Int,0,strContent.Length),
BAWebLib.Database.MakeInParam("@faceicon",SqlDbType.TinyInt,0,intFaceIcon),
BAWebLib.Database.MakeInParam("@underwriteflag",SqlDbType.Bit,0,BooUnderWrite),
BAWebLib.Database.MakeInParam("@notreplay",SqlDbType.Bit,0,BooLock),
BAWebLib.Database.MakeInParam("@LastInfo",SqlDbType.NVarChar,100,strLastInfo),
BAWebLib.Database.MakeInParam("@BoardID",SqlDbType.Int,0,intBoardID),
BAWebLib.Database.MakeInParam("@AnnounceID",SqlDbType.Int,0,intAnnounceID)
};
BAWebLib.Database.ExecuteNonQuery(sb.ToString(),param);

[aspx_vb]

SqlHelper.cs是什么东西，那里有这个东东，有没有VB.net版的呢。

[aspx_vb]

TO: evaELLIS()

现在公司没什么事做，这几天想把数据库操作(读、增、删、改)都写成类文件以节约下次写代码的时间。

[Edifier0709]

到微軟網站上有

[vzxq]

Microsoft Application Blocks for .NET
vb,c#都有

[aspx_vb]

只要数据库操作(读、增、删、改)，想自己写一个，大家以前都没有做过这个吗？？？

我听说程序员几乎都自己有一个叫什么结构的类呀。

[xiaoniaofei]

找
Data Access Application Block
！

呵呵，用的很爽！！
下载http://www.8965.com

[morality]

顶

[aspx_vb]

Data Access Application Block
介绍一下用法吧，如要打开一个数据连接的代码怎么写。