《.NET实例开发》实验指导书 - 图文(7)

{

OleDbCommand cmd = new OleDbCommand(); OleDbTransaction tran = null; cmd.Transaction = tran; try {

conn.Open();

tran = conn.BeginTransaction(); cmd.Connection = conn; cmd.Transaction = tran;

Int32 count = Sqlstr.Length;

for (Int32 i = 0; i < count; i ++ ) {

cmd.CommandText = Sqlstr[i];

cmd.Parameters.AddRange(param[i]); cmd.ExecuteNonQuery(); }

tran.Commit(); return 1; } catch {

tran.Rollback(); return 0; }

finally {

cmd.Dispose(); conn.Close(); } } } } }

下面为SQLDbHelper.cs文件添加代码，首先在SQLDbHelper.cs文件的开始部分添加以下命名空

间的引用：

using System.Configuration; using System.Data;

using System.Data.OleDb; using System.Web; 然后，将SQLDbHelper类的访问属性改为“public”，将命名空间改为“CSharpStudy.DBUtility”，

30

再添加OLEDBDAAB代码，最后SQLDbHelper.cs的内容如下： using System;

using System.Collections.Generic; using System.Text;

using System.Configuration; using System.Data;

using System.Data.SqlClient; using System.Web;

namespace CSharpStudy.DBUtility {

public class SQLDbHelper {

public SQLDbHelper() { }

///

/// 返回数据库连接字符串 ///

///

public static String GetSqlConnection() {

String conn = ConfigurationSettings.AppSettings[\ return conn; }

///

/// 获得参数对象 ///

/// /// /// /// /// ///

public static SqlParameter GetParameter(String paramName,SqlDbType paramType, Int32

paramSize, String ColName, Object paramValue) {

SqlParameter param = new SqlParameter(paramName, paramType, paramSize, ColName); param.Value = paramValue; return param; }

///

/// 获得参数对象

31

///

/// /// /// /// ///

public static SqlParameter GetParameter(String paramName, SqlDbType paramType, Int32

paramSize, String ColName) {

SqlParameter param = new SqlParameter(paramName, paramType, paramSize, ColName); return param; }

///

/// 获得参数对象 ///

/// /// /// /// ///

public static SqlParameter GetParameter(String paramName, SqlDbType paramType, Object

paramValue) {

SqlParameter param = new SqlParameter(paramName, paramType); param.Value = paramValue; return param; }

///

/// 执行SQL语句 ///

/// /// ///

public static int ExecuteSql(String Sqlstr, SqlParameter[] param) {

String ConnStr = SQLDbHelper.GetSqlConnection();

using (SqlConnection conn = new SqlConnection(ConnStr)) {

SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = Sqlstr;

cmd.Parameters.AddRange(param);

32

conn.Open();

cmd.ExecuteNonQuery(); conn.Close(); return 1; } }

///

/// 执行SQL语句并返回数据表 ///

/// ///

public static DataTable ExecuteDt(String Sqlstr) {

String ConnStr = SQLDbHelper.GetSqlConnection();

using (SqlConnection conn = new SqlConnection(ConnStr)) {

SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn); DataTable dt = new DataTable(); conn.Open(); da.Fill(dt); conn.Close(); return dt; } }

///

/// 执行SQL语句并返回数据表 ///

/// /// ///

public static DataTable ExecuteDt(String Sqlstr, SqlParameter[] param) {

String ConnStr = SQLDbHelper.GetSqlConnection();

using (SqlConnection conn = new SqlConnection(ConnStr)) {

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter();

SqlCommand cmd = new SqlCommand(Sqlstr, conn); cmd.Connection = conn;

cmd.Parameters.AddRange(param); da.SelectCommand = cmd; conn.Open();

33

da.Fill(dt); conn.Close(); return dt; } }

///

/// 批量执行SQL语句 ///

/// /// ///

public static Int32 ExecuteSqls(String[] Sqlstr, List param) {

String ConnStr = SQLDbHelper.GetSqlConnection();

using (SqlConnection conn = new SqlConnection(ConnStr)) {

SqlCommand cmd = new SqlCommand(); SqlTransaction tran = null; cmd.Transaction = tran; try {

conn.Open();

tran = conn.BeginTransaction(); cmd.Connection = conn; cmd.Transaction = tran;

Int32 count = Sqlstr.Length;

for (Int32 i = 0; i < count; i ++ ) {

cmd.CommandText = Sqlstr[i];

cmd.Parameters.AddRange(param[i]); cmd.ExecuteNonQuery(); }

tran.Commit(); return 1; } catch {

tran.Rollback(); return 0; }

finally

34