MySQLConnection conn = new MySQLConnection( new MySQLConnectionString("localhost","","root","").AsString ); conn.Open(); MySQLCommand cmd; cmd = new MySQLDriverCS.MySQLCommand("",conn); Mysql 操作类
using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Configuration; using System.Data; using System.Collections; using System.Net;///SQL的帮助类,用来执行数据库操作语句 ///作者:汤雄 ///时间:2009年5月29日namespace AccessSQL { public class SQLHelper {
public static string ConnectionString = "Data Source=192.168.0.37;uid=sa;pwd=sa;Initial Catalog=aaaao"; public static SqlConnection conn = new SqlConnection(ConnectionString); // /// <summary> /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <res> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </res> /// <param name="connectionString">a valid connection string for a SqlConnection</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 SqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static bool Open() { try { if (conn.State != ConnectionState.Open) conn.Open(); return true; } catch { return false; } } public static bool Closed() { try { if (conn.State != ConnectionState.Closed) conn.Close(); return true; } catch { return false; } } public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); try {
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val;
} } /// <summary> /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction /// using the provided parameters. /// </summary> /// <res> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </res> /// <param name="trans">an existing sql transaction</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 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 trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// Execute a SqlCommand that returns a resultset against the database specified in the connection string /// using the provided parameters. /// </summary> /// <res> /// e.g.: /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </res> /// <param name="connectionString">a valid connection string for a SqlConnection</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 SqlParamters used to execute the command</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); // SqlConnection conn = new SqlConnection(connectionString); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch {
conn.Close(); throw; } } public static SqlDataAdapter ExecuteAdapter(string connectionString, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters); try { SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; return da; } catch { conn.Close(); throw; } } /// <summary> /// Prepare a command for execution /// </summary> /// <param name="cmd">SqlCommand object</param> /// <param name="conn">SqlConnection object</param> /// <param name="trans">SqlTransaction object</param> /// <param name="cmdType">Cmd type e.g. stored procedure or text</param> /// <param name="cmdText">Command text, e.g. Select * from Products</param> /// <param name="cmdParms">SqlParameters to use in the command</param> 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); } } } }
我是来拿分的using System; using System.Collections; using System.Collections.Specialized; using System.Data; using System.Data.SqlClient; using System.Configuration; public static string connectionString = "Data Source=192.168.1.235;Initial Catalog=jnbuild;Persist Security Info=True;User ID=sa;Password=sa";
public SqlHelper() { } #region 公用方法 /// <summary> /// 获取某个表的数量 /// </summary> /// <param name="field">主键</param> /// <param name="tableName">表名</param> /// <param name="where">条件</param> /// <returns></returns> public static int GetDataRecordCount(string field, string tableName, string where) { string strsql = "select count(" + field + ") from " + tableName; if (where != "") { strsql += " where " + where; } object obj = SqlHelper.GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } public static int GetMaxID(string FieldName, string TableName) { string strsql = "select max(" + FieldName + ")+1 from " + TableName; object obj = SqlHelper.GetSingle(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } public static bool Exists(string strSql) { object obj = SqlHelper.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 = SqlHelper.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语句 /// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> 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 new Exception(E.Message); //ITNB.Base.Error.showError(E.Message.ToString()); } } } } /// <summary> /// 执行SQL语句,设置命令的执行等待时间 /// </summary> /// <param name="SQLString"></param> /// <param name="Times"></param> /// <returns></returns> 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 new Exception(E.Message); // ITNB.Base.Error.showError(E.Message.ToString()); } } } } /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.SqlClient.SqlException E) { tx.Rollback(); throw new Exception(E.Message); // ITNB.Base.Error.showError(E.Message.ToString()); } } } /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param> /// <returns>影响的记录数</returns> 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 new Exception(E.Message); // ITNB.Base.Error.showError(E.Message.ToString()); } finally { cmd.Dispose(); connection.Close(); } } }
还需要一个APP.config配置文件<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="SQLCONNECTIONSTRING" value="server=.\SQLEXPRESS;uid=你的;pwd=你的;database=数据库"/>
</appSettings>
</configuration>using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;namespace DAL
{
public class DBHelp
{
public SqlConnection Connection;//数据库连接
public string connectionString;
public bool m_bln; //函数执行成功的标志
public int rows; //返回 执行后,记录的行数
public DBHelp()
{
connectionString = System.Configuration.ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString();
//connectionString = System.Configuration.ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"].ToString();
//connectionString = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString.ToString();
Connection = new SqlConnection(connectionString);
}
public int ExecuteCommand(string sql)
{
int relust = 0;
if (Connection.State != ConnectionState.Open)
Connection.Open();
using (SqlCommand command = new SqlCommand(sql, Connection))
{ command.CommandTimeout = 0;
relust = command.ExecuteNonQuery();
}
return relust;
}
public bool ExecuteOperateData(string sql)
{
try
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
SqlCommand cmd = Connection.CreateCommand();
//SqlCommand coom = new SqlCommand(sql, Connection);
//coom.ExecuteNonQuery();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
rows = cmd.ExecuteNonQuery();
// Connection.Dispose();
m_bln = true;
return m_bln;
}
catch//(Exception ex)
{
//MessageBox.Show(ex.ToString());
m_bln = false;
return m_bln;
}
}
public DataTable ReturnRs(string sql)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
if (Connection.State != ConnectionState.Open) Connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = new SqlCommand(sql, Connection);
sqlDA.Fill(ds);
dt = ds.Tables[0]; }
catch//(Exception ex)
{
//MessageBox.Show(ex.ToString());
dt = null;
}
finally
{ Connection.Close();
}
return dt;
}
public string OneResult(string sql)
{
if (Connection.State != ConnectionState.Open) Connection.Open();
SqlCommand cmd = Connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
string theResult = "";
if (cmd.ExecuteScalar() != null)
{
theResult = cmd.ExecuteScalar().ToString(); }
Connection.Close();
return theResult;
}
public DataTable exeonesql(string sql)
{
SqlDataAdapter da;
DataSet ds;
if (Connection.State != ConnectionState.Open) Connection.Open();
using (SqlCommand cmd = new SqlCommand(sql, Connection))
{
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
}
Connection.Close();
da.Dispose();
return ds.Tables[0];
}
}
}
你自己有什么地方可能需要改一下.
你好,我找到了,现在有个问题是:
MySqlCommand cmd = new MySqlCommand();
XmlReader retval = cmd.ExecuteXmlReader();
为什么 MySqlCommand的对象 点不出来ExecuteXmlReader()这个方法,能不能帮我解决一下?
MySQLConnection conn = new MySQLConnection( new MySQLConnectionString("localhost","","root","").AsString );
conn.Open();
MySQLCommand cmd;
cmd = new MySQLDriverCS.MySQLCommand("",conn);
Mysql 操作类
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Collections;
using System.Net;///SQL的帮助类,用来执行数据库操作语句
///作者:汤雄
///时间:2009年5月29日namespace AccessSQL
{
public class SQLHelper
{
public static string ConnectionString = "Data Source=192.168.0.37;uid=sa;pwd=sa;Initial Catalog=aaaao";
public static SqlConnection conn = new SqlConnection(ConnectionString);
//
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <res>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </res>
/// <param name="connectionString">a valid connection string for a SqlConnection</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 SqlParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static bool Open()
{
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
return true;
}
catch
{
return false;
}
}
public static bool Closed()
{
try
{
if (conn.State != ConnectionState.Closed)
conn.Close();
return true;
}
catch
{
return false;
}
}
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{ SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
catch(Exception e)
{
return 0;
}
finally
{
cmd.Parameters.Clear();
}
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <res>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </res>
/// <param name="trans">an existing sql transaction</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 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 trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
} /// <summary>
/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <res>
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </res>
/// <param name="connectionString">a valid connection string for a SqlConnection</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 SqlParamters used to execute the command</param>
/// <returns>A SqlDataReader containing the results</returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
// SqlConnection conn = new SqlConnection(connectionString); // we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
} public static SqlDataAdapter ExecuteAdapter(string connectionString, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
return da;
}
catch
{
conn.Close();
throw;
}
} /// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">SqlCommand object</param>
/// <param name="conn">SqlConnection object</param>
/// <param name="trans">SqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">SqlParameters to use in the command</param>
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);
}
}
}
}
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public static string connectionString = "Data Source=192.168.1.235;Initial Catalog=jnbuild;Persist Security Info=True;User ID=sa;Password=sa";
public SqlHelper()
{ } #region 公用方法
/// <summary>
/// 获取某个表的数量
/// </summary>
/// <param name="field">主键</param>
/// <param name="tableName">表名</param>
/// <param name="where">条件</param>
/// <returns></returns>
public static int GetDataRecordCount(string field, string tableName, string where)
{
string strsql = "select count(" + field + ") from " + tableName;
if (where != "")
{
strsql += " where " + where;
}
object obj = SqlHelper.GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = SqlHelper.GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
public static bool Exists(string strSql)
{
object obj = SqlHelper.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 = SqlHelper.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语句 /// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
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 new Exception(E.Message);
//ITNB.Base.Error.showError(E.Message.ToString());
}
}
}
} /// <summary>
/// 执行SQL语句,设置命令的执行等待时间
/// </summary>
/// <param name="SQLString"></param>
/// <param name="Times"></param>
/// <returns></returns>
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 new Exception(E.Message);
// ITNB.Base.Error.showError(E.Message.ToString());
}
}
}
} /// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
// ITNB.Base.Error.showError(E.Message.ToString());
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
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 new Exception(E.Message);
// ITNB.Base.Error.showError(E.Message.ToString());
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
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 new Exception(E.Message);
// ITNB.Base.Error.showError(E.Message.ToString());
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
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 new Exception(E.Message);
//ITNB.Base.Error.showError(E.Message.ToString());
}
finally
{
cmd.Dispose();
connection.Close();
}
}
} /// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
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 new Exception(e.Message);
// ITNB.Base.Error.showError(e.Message.ToString());
}
}
}
}
/// <summary>
/// 执行查询语句,返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connection);
try
{
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
// ITNB.Base.Error.showError(e.Message.ToString());
}
//finally //不能在此关闭,否则,返回的对象将无法使用
//{
// cmd.Dispose();
// connection.Close();
//}
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
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 E)
{
throw new Exception(E.Message);
// ITNB.Base.Error.showError(E.Message.ToString());
}
return ds;
}
}
/// <summary>
/// 执行查询语句,返回DataSet,设置命令的执行等待时间
/// </summary>
/// <param name="SQLString"></param>
/// <param name="Times"></param>
/// <returns></returns>
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 E)
{
throw new Exception(E.Message);
// ITNB.Base.Error.showError(ex.Message.ToString());
}
return ds;
}
}
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
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;
}
}
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
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 new Exception(e.Message);
// ITNB.Base.Error.showError(e.Message.ToString());
}
}
}
} /// <summary>
/// 执行查询语句,返回SqlDataReader (使用该方法切记要手工关闭SqlDataReader和连接)
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
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();
cmd.Parameters.Clear();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
// ITNB.Base.Error.showError(e.Message.ToString());
}
//finally //不能在此关闭,否则,返回的对象将无法使用
//{
// cmd.Dispose();
// connection.Close();
//} } /// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
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 E)
{
throw new Exception(E.Message);
// ITNB.Base.Error.showError(ex.Message.ToString());
}
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 存储过程操作 /// <summary>
/// 执行存储过程 (使用该方法切记要手工关闭SqlDataReader和连接)
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
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();
//Connection.Close(); 不能在此关闭,否则,返回的对象将无法使用
return returnReader; }
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
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;
}
}
/// <summary>
/// 执行存储过程后返回执行结果(标识)
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static string RunProcedureState(string storedProcName, IDataParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{ connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); //增加存储过程的返回值参数
sqlDA.SelectCommand.ExecuteNonQuery();
connection.Close();
return sqlDA.SelectCommand.Parameters["ReturnValue"].Value.ToString();
}
}
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // 创建SqlCommand命令,并进行预处理
MySqlCommand cmd = new MySqlCommand();
MySqlCommand c = new MySqlCommand ();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // 执行命令
XmlReader retval = cmd.ExecuteXmlReader(); // 清除参数,以便再次使用.
cmd.Parameters.Clear();
return retval;
}是在 XmlReader retval = cmd.ExecuteXmlReader();这个地方的时候 点不出来ExecuteXmlReader()方法,不知道 MySqlCommand 这个类里有没有,我查了查资料也没有搞清楚,希望大家看清楚问题,谢谢了