110,561
社区成员
发帖
与我相关
我的任务
分享
<?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];
}
}
}
public static XmlReader ExecuteXmlReader(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
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;
}
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>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <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>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <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>
/// <remarks>
/// e.g.:
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <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);
}
}
}
}