怎样让connection.Execute(sqlcmd)中的sqlcmd可以有换行？

deldelmeme979 2002-07-02 10:22:55
为了在Sql server中执行如下代码：

delete from store where Num=2
go
delete from store where Num=3
go
...
(我这是用一个循环得出的语句)

我把上面这些语句赋给sqlcmd字符串，然后建立一个connection连接，想通过Connection.execute(sqlcmd)来同时执行上面的语句，但很明显这样出来的语句在go前面是没有换行效果的，而一但没有换行符则sql server就会报错。因此想请教大家，怎么样才能像sql server下达包括换行符效果的上述命令？谢谢！
...全文
133 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
zephyr_zhao 2002-07-03
  • 打赏
  • 举报
 回复
strSQL=""
strSQL=strSQL&"SET XACT_ABORT ON\n";
strSQL=strSQL&"BEGIN TRANSACTION\n";
strSQL=strSQL&"INSERT INTO Orders(OrdID,CustID,OrdDat) VALUES('9999','1234',GETDATE())\n";
strSQL=strSQL&"INSERT INTO OrderRows(OrdID,OrdRow,Item,Qty) VALUES('9999','01','G4385',5)\n";
strSQL=strSQL&"INSERT INTO OrderRows(OrdID,OrdRow,Item,Qty) VALUES('9999','02','G4726',1)\n";
strSQL=strSQL&"COMMIT TRANSACTION\n";
strSQL=strSQL&"SET XACT_ABORT OFF\n";
oConn.Execute(strSQL);
julyclyde 2002-07-02
  • 打赏
  • 举报
 回复
你可以编辑字符串时加vbCrlf表示

SQL并不要求一定要换行。用分号也是可以的
deldelmeme979 2002-07-02
  • 打赏
  • 举报
 回复
to zephyr_zhao(zephyr)：
你的办法让我很受启发。只不过我现在太想知道怎样使用存储过程来解决了，能帮帮我吗？
zephyr_zhao 2002-07-02
  • 打赏
  • 举报
 回复
为何不写成:
delete from store where Num=2 or Num=3 or Num=4...
deldelmeme979 2002-07-02
  • 打赏
  • 举报
 回复
谢谢上面几位的指教，可是我真的不懂存储过程，可以按照这个例子给我写段示范代码吗？真的谢谢！
phili1999 2002-07-02
  • 打赏
  • 举报
 回复
既然这样 你用个存储过程吧这样既解决了你的问题而且是访问速度也便快了
zyhowe 2002-07-02
  • 打赏
  • 举报
 回复
sql &_

sql &_
gq 2002-07-02
  • 打赏
  • 举报
 回复
存储过程。
S.F. 2002-07-02
  • 打赏
  • 举报
 回复

cnn.execute(sql1 & ";")
cnn.execute(sql2 & ";")
zephyr_zhao 2002-07-02
  • 打赏
  • 举报
 回复
SQL Server我不会
DBHelper方便连接数据库 DBHelper
using System; using System.Collections; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace MyOfficeDAL { public static class DBHelper { private static SqlConnection connection; public static SqlConnection Connection { get { string connectionString = ConfigurationManager.ConnectionStrings["MyOfficeConnectionString"].ConnectionString; connection = new SqlConnection(connectionString); if (connection == null) { connection.Open(); } else if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } else if (connection.State == System.Data.ConnectionState.Broken) { connection.Close(); connection.Open(); } return connection; } } public static int ExecuteCommand(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = cmd.ExecuteNonQuery(); return result; } public static int ExecuteCommand(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); return cmd.ExecuteNonQuery(); } public static string ReturnStringScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); try { string result = cmd.ExecuteScalar().ToString(); return result; } catch (Exception ex) { return "0"; } connection.Close(); } public static int GetScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); try { int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } catch (Exception e) { return 0; } connection.Close(); } public static int GetScalar(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; connection.Close(); } public static SqlDataReader GetReader(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataReader reader = cmd.ExecuteReader(); return reader; reader.Close(); reader.Dispose(); } public static SqlDataReader GetReader(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataReader reader = cmd.ExecuteReader(); return reader; reader.Close(); reader.Dispose(); } public static DataTable GetDataSet(string safeSql) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); connection.Close(); connection.Dispose(); return ds.Tables[0]; } public static DataTable GetDataSet(string sql, params SqlParameter[] values) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); connection.Close(); connection.Dispose(); return ds.Tables[0]; } } }
C#访问数据库资源库（支持Access、MSSql、MySql、Oracle等）让数据库访问更便捷，更稳定
C#访问数据库资源库（支持Access、MSSql、MySql、Oracle等）让数据库访问更便捷，更稳定 如果需要源代码，可通过邮箱cj5260@163.com索取 访问Access数据库方法 UGM.Common.DAL.DB.DBAccessDAL.string getIdentity(OleDbConnection Conn, OleDbTransaction Tran) UGM.Common.DAL.DB.DBAccessDAL.Connect(string DBSource) UGM.Common.DAL.DB.DBAccessDAL.check(OleDbConnection Conn) UGM.Common.DAL.DB.DBAccessDAL.Excute(OleDbConnection Conn, string CommandText) UGM.Common.DAL.DB.DBAccessDAL.convetAccess(string str) UGM.Common.DAL.DB.DBAccessDAL.Analyse(string FieldType) UGM.Common.DAL.DB.DBAccessDAL.ExecuteSql(string SQLString) UGM.Common.DAL.DB.DBAccessDAL.ExecuteSql(string ConnString, string SQLString) UGM.Common.DAL.DB.DBAccessDAL.ExecuteSql(OleDbConnection Conn, string SQLString) UGM.Common.DAL.DB.DBAccessDAL.ExecuteSql(OleDbConnection Conn, OleDbTransaction Tran, string SQLString) UGM.Common.DAL.DB.DBAccessDAL.ExecuteSqlTran(ArrayList SQLStringList) UGM.Common.DAL.DB.DBAccessDAL.ExecuteSqlTran(string ConnString, ArrayList SQLStringList) UGM.Common.DAL.DB.DBAccessDAL.GetSingle(string SQLString) UGM.Common.DAL.DB.DBAccessDAL.GetSingle(string ConnString, string SQLString) UGM.Common.DAL.DB.DBAccessDAL.Query(string SQLString) UGM.Common.DAL.DB.DBAccessDAL.Query(string ConnString, string SQLString) UGM.Common.DAL.DB.DBAccessDAL.Query(string SQLString, int start, int max) UGM.Common.DAL.DB.DBAccessDAL.Query(string ConnString, string SQLString, int start, int max) UGM.Common.DAL.DB.DBAccessDAL.Query(OleDbConnection Conn, string SQLString) UGM.Common.DAL.DB.DBAccessDAL.Query(OleDbConnection Conn, OleDbTransaction Tran, string SQLString) UGM.Common.DAL.DB.DBAccessDAL.ExecuteSql(string SQLString, params OleDbParameter[] cmdParms) UGM.Common.DAL.DB.DBAccessDAL.ExecuteSql(string ConnString, string SQLString, params OleDbParameter[] cmdParms) UGM.Common.DAL.DB.DBAccessDAL.ExecuteSqlTran(Hashtable SQLStringList) UGM.Common.DAL.DB.DBAccessDAL.ExecuteSqlTran(string ConnString, Hashtable SQLStringList) UGM.Common.DAL.DB.DBAccessDAL.GetSingle(string SQLString
在窗体中建一个文本框和时间控件,可以在文本框中显示跳动的服务器时间
Private Sub Form_Load() Timer1.Interval = 300 End Sub Private Sub Timer1_Timer() Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rst As New ADODB.Recordset Dim Sqlser_time cnn.ConnectionString = "driver={SQL Server};" & _ "server=server;uid=sa;pwd=;database=master" cnn.Open cmd.ActiveConnection = cnn cmd.CommandText = "SELECT GETDATE() AS sys_Sqlser_time" Set rst = cmd.Execute Sqlser_time = rst(0) Text1.Text = Sqlser_time rst.Close cnn.Close End Sub
CMS.DBUtility.dll
一款特别好用的.net数据库链接通用类。 using System; using System.Collections; using System.Collections.Specialized; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Data.Common; using System.Collections.Generic; namespace CMS.DBUtility { /// /// 数据访问抽象基础类 /// Copyright (C) 2004-2008 By LiTianPing /// public abstract class DbHelperSQL { //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. public static string connectionString = PubConstant.ConnectionString; public DbHelperSQL() { } #region 公用方法 /// /// 判断是否存在某表的某个字段 /// /// 表名称 /// 列名称 /// 是否存在 public static bool ColumnExists(string tableName, string columnName) { string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'"; object res = GetSingle(sql); if (res == null) { return false; } return Convert.ToInt32(res) > 0; } public static int GetMaxID(string FieldName, string TableName) { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } public static bool Exists(string strSql) { object obj = GetSingle(strSql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } /// /// 表是否存在 /// /// /// public static bool TabExists(string TableName) { string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1"; //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')"; object obj = GetSingle(strsql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } public static bool Exists(string strSql, params SqlParameter[] cmdParms) { object obj = GetSingle(strSql, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 执行简单SQL语句 /// /// 执行SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 public static int ExecuteSql(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } public static int ExecuteSqlByTime(string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } /// /// 执行Sql和Oracle滴混合事务 /// /// SQL命令行列表 /// Oracle命令行列表 /// 执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功 public static int ExecuteSqlTran(List list, List oracleCmdSqlList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { foreach (CommandInfo myDE in list) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; PrepareCommand(cmd, conn, tx, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.SolicitationEvent) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式"); //return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (isHave) { //引发事件 myDE.OnSolicitationEvent(); } } if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); //return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); //return 0; } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); //return 0; } continue; } int val = cmd.ExecuteNonQuery(); if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { tx.Rollback(); throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); //return 0; } cmd.Parameters.Clear(); } string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC"); bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList); if (!res) { tx.Rollback(); throw new Exception("Oracle执行失败"); // return -1; } tx.Commit(); return 1; } catch (System.Data.SqlClient.SqlException e) { tx.Rollback(); throw e; } catch (Exception e) { tx.Rollback(); throw e; } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// 多条SQL语句 public static int ExecuteSqlTran(List SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } } } /// /// 执行带一个存储过程参数的的SQL语句。 /// /// SQL语句 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 /// 影响的记录数 public static int ExecuteSql(string SQLString, string content) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// /// 执行带一个存储过程参数的的SQL语句。 /// /// SQL语句 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 /// 影响的记录数 public static object ExecuteSqlGet(string SQLString, string content) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(SQLString, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// /// SQL语句 /// 图像字节,数据库的字段类型为image的情况 /// 影响的记录数 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(strSQL, connection); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException e) { throw e; } finally { cmd.Dispose(); connection.Close(); } } } /// /// 执行一条计算查询结果语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） public static object GetSingle(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } public static object GetSingle(string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); cmd.CommandTimeout = Times; object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { connection.Close(); throw e; } } } } /// /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// /// 查询语句 /// SqlDataReader public static SqlDataReader ExecuteReader(string strSQL) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(strSQL, connection); try { connection.Open(); SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return myReader; } catch (System.Data.SqlClient.SqlException e) { throw e; } } /// /// 执行查询语句，返回DataSet /// /// 查询语句 /// DataSet public static DataSet Query(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } public static DataSet Query(string SQLString, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); command.SelectCommand.CommandTimeout = Times; command.Fill(ds, "ds"); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } #endregion #region 执行带参数的SQL语句 /// /// 执行SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (System.Data.SqlClient.SqlException e) { throw e; } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） public static void ExecuteSqlTran(Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） public static int ExecuteSqlTran(System.Collections.Generic.List cmdList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int count = 0; //循环 foreach (CommandInfo myDE in cmdList) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { trans.Rollback(); return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { trans.Rollback(); return 0; } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { trans.Rollback(); return 0; } continue; } int val = cmd.ExecuteNonQuery(); count += val; if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { trans.Rollback(); return 0; } cmd.Parameters.Clear(); } trans.Commit(); return count; } catch { trans.Rollback(); throw; } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int indentity = 0; //循环 foreach (CommandInfo myDE in SQLStringList) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// /// 执行多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int indentity = 0; //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } /// /// 执行一条计算查询结果语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） public static object GetSingle(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { throw e; } } } } /// /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// /// 查询语句 /// SqlDataReader public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return myReader; } catch (System.Data.SqlClient.SqlException e) { throw e; } // finally // { // cmd.Dispose(); // connection.Close(); // } } /// /// 执行查询语句，返回DataSet /// /// 查询语句 /// DataSet public static DataSet Query(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return ds; } } } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, 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 = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (SqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } #endregion #region 存储过程操作 /// /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) /// /// 存储过程名 /// 存储过程参数 /// SqlDataReader public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) { SqlConnection connection = new SqlConnection(connectionString); SqlDataReader returnReader; connection.Open(); SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); return returnReader; } /// /// 执行存储过程 /// /// 存储过程名 /// 存储过程参数 /// DataSet结果中的表名 /// DataSet public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); sqlDA.SelectCommand.CommandTimeout = Times; sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } /// /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) /// /// 数据库连接 /// 存储过程名 /// 存储过程参数 /// SqlCommand private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } return command; } /// /// 执行存储过程，返回影响的行数 /// /// 存储过程名 /// 存储过程参数 /// 影响的行数 /// public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) { using (SqlConnection connection = new SqlConnection(connectionString)) { int result; connection.Open(); SqlCommand command = BuildIntCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return result; } } /// /// 创建 SqlCommand 对象实例(用来返回一个整数值) /// /// 存储过程名 /// 存储过程参数 /// SqlCommand 对象实例 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return command; } #endregion } }
System.Data.Silverlight V2.1
一、升级说明 1、修复了数据库中字段值为空值的时候查询报错的Bug； 2、修复DbCommand属性ExecuteType为DbExecuteType.Scalar时执行命令报错的Bug； 3、感谢网友“尘世流浪汉”和“春之子”反馈Bug，也欢迎大家试用并提出更多建议！ 二、修复功能示例 1、空值测试 public class T_Test { public int? Id { get; set; } public string Name { get; set; } } try { //创建一个数据连接 DbConnection conn = new DbConnection("Data Source=|DataDirectory|CSmsPlatThird.db;Pooling=true;FailIfMissing=false"); //设置使用的数据访问程序集 conn.AssemblyName = "System.Data.SQLite"; //设置数据工厂，这里是SQLite的数据工厂 conn.DbProviderFactory = "System.Data.SQLite.SQLiteFactory"; //创建一个数据命令 DbCommandSyn cmd = new DbCommandSyn(); //设置命令的连接 cmd.Connection = conn; //设置SQL语句，可以是存储过程 cmd.CommandText = "SELECT [Id],[Name] FROM [T_Test]"; //设置命令类型，一般SQL语句是Text，存储过程是StoredProcedure cmd.CommandType = DbCommandType.Text; //设置执行类型 cmd.ExecuteType = DbExecuteType.Reader; //执行命令，得到结果 DbCommandExecuteResult result = cmd.Execute(); if (!string.IsNullOrEmpty(result.ErrMsg))//首先判断ErrMsg是否有值，有表示执行过程发生错误 { MessageBox.Show("发生错误：" + result.ErrMsg); } else { List valueList = result.ReaderResult.ToEntityList(); //将数据显示在DataGrid中 this.dataGrid1.ItemsSource = valueList; } } catch (Exception ex) { MessageBox.Show("发生错误：" + ex.ToString()); } 2、存储过程示例 try { //数据库创建T_SQL脚本在网站App_Data文件夹下面，文件名为OMSDB.sql DbConnection conn = new DbConnection("Server=localhost;DataBase=OMSDB;Uid=sa;Pwd=jiton;"); DbCommandSyn cmd = new DbCommandSyn(); cmd.Connection = conn; //设置存储过程名称 cmd.CommandText = "JP_GetSystemName"; //设置命令类型为存储过程 cmd.CommandType = DbCommandType.StoredProcedure; cmd.ExecuteType = DbExecuteType.Scalar; //执行命令，得到结果 DbCommandExecuteResult result = cmd.Execute(); if (!string.IsNullOrEmpty(result.ErrMsg))//首先判断是否存在错误 { MessageBox.Show("发生错误：" + result.ErrMsg); } else { MessageBox.Show("系统名称为：" + result.ScalarResult as string); } } catch (Exception ex) { MessageBox.Show("发生错误：" + ex.Message); } 三、技术交流 有任何问题可以加入唯一指定的专用QQ群153079750进行反馈交流，也欢迎加入笔者的另一个Silverlight技术群175213051进行交流。
ASP

28,391

社区成员

357,059

社区内容

发帖
与我相关
我的任务
社区描述
ASP即Active Server Pages，是Microsoft公司开发的服务器端脚本环境。
社区管理员
  • ASP
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章