using System;
|
using System.Collections.Generic;
|
using System.Text;
|
using System.Data.SqlClient;
|
using System.Data;
|
using System.Collections;
|
using System.Configuration;
|
|
namespace SQLHelper
|
{
|
public static class SQLHelper222
|
{
|
public static SqlConnection Conn;
|
public static string strCon ="";
|
|
|
|
|
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;
|
}
|
|
|
|
|
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
|
{
|
SqlCommand command = new SqlCommand(storedProcName, connection);
|
command.CommandType = CommandType.StoredProcedure;
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
if (parameter != null)
|
{
|
if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
|
{
|
parameter.Value = DBNull.Value;
|
}
|
command.Parameters.Add(parameter);
|
}
|
}
|
}
|
return command;
|
}
|
|
|
|
public static void close()
|
{
|
if ((Conn != null) && (Conn.State == ConnectionState.Open))
|
{
|
Conn.Close();
|
Conn.Dispose();
|
}
|
}
|
|
|
|
|
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
|
{
|
SqlCommand cmd = new SqlCommand();
|
string strCon = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
|
using (Conn = new SqlConnection(strCon))
|
{
|
PrepareCommand(cmd, Conn, null, cmdType, cmdText, commandParameters);
|
int num = cmd.ExecuteNonQuery();
|
cmd.Parameters.Clear();
|
Conn.Close();
|
Conn.Dispose();
|
return num;
|
}
|
}
|
|
|
|
|
public static SqlConnection getConn()
|
{
|
string sServer = Pub_Class.ClsIni.ReadIni("Erp", "Server", Pub_Class.ClsPub.AppPath + @"\" + "HXErp.ini");
|
string sDataBase = Pub_Class.ClsIni.ReadIni("Erp", "DataBase", Pub_Class.ClsPub.AppPath + @"\" + "HXErp.ini");
|
string sUser = Pub_Class.ClsIni.ReadIni("Erp", "UserName", Pub_Class.ClsPub.AppPath + @"\" + "HXErp.ini");
|
string sPassword = Pub_Class.ClsIni.ReadIni("Erp", "PassWord", Pub_Class.ClsPub.AppPath + @"\" + "HXErp.ini");
|
string strCon = "Data Source=" + sServer + ";DataBase="+sDataBase+";User ID=" + sUser + ";PWD=" + sPassword + " ";
|
return new SqlConnection(strCon);
|
}
|
|
|
|
|
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 parameter in cmdParms)
|
{
|
cmd.Parameters.Add(parameter);
|
}
|
}
|
}
|
|
public static DataSet Query(string SQLString, string tableName)
|
{
|
string strCon = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
|
using (SqlConnection connection = new SqlConnection(strCon))
|
{
|
DataSet dataSet = new DataSet();
|
try
|
{
|
connection.Open();
|
new SqlDataAdapter(SQLString, connection).Fill(dataSet, tableName);
|
connection.Close();
|
}
|
catch (SqlException exception)
|
{
|
throw new Exception(exception.Message);
|
}
|
finally
|
{
|
connection.Close();
|
}
|
return dataSet;
|
}
|
}
|
|
public static DataTable ExecuteDataTable(string sSQL)
|
{
|
DataTable dt = new DataTable();
|
|
SqlDataAdapter sqldpt = new SqlDataAdapter();
|
SqlConnection conn = getConn();
|
SqlCommand cmd = new SqlCommand(sSQL, conn);
|
try
|
{
|
//cmd.CommandText = sSQL;
|
sqldpt.SelectCommand = cmd;
|
conn.Open();
|
sqldpt.Fill(dt);
|
conn.Close();
|
conn.Dispose();
|
}
|
catch
|
{
|
conn.Close();
|
conn.Dispose();
|
throw;
|
}
|
finally
|
{
|
conn.Close();
|
}
|
|
return dt;
|
}
|
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;
|
}
|
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
|
{
|
SqlConnection connection = getConn();
|
try
|
{
|
connection.Open();
|
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
|
command.CommandType = CommandType.StoredProcedure;
|
return command.ExecuteReader(CommandBehavior.CloseConnection);
|
}
|
catch (Exception e)
|
{
|
throw new Exception(e.Message);
|
return null;
|
|
}
|
}
|
|
|
|
|
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
|
{
|
string strCon = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
|
using (SqlConnection connection = new SqlConnection(strCon))
|
{
|
connection.Open();
|
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
|
rowsAffected = command.ExecuteNonQuery();
|
int num = (int)command.Parameters["ReturnValue"].Value;
|
connection.Close();
|
return num;
|
}
|
}
|
|
|
|
|
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
|
{
|
string strCon = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
|
using (SqlConnection connection = new SqlConnection(strCon))
|
{
|
DataSet dataSet = new DataSet();
|
connection.Open();
|
SqlDataAdapter adapter = new SqlDataAdapter();
|
adapter.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
|
adapter.Fill(dataSet, tableName);
|
connection.Close();
|
connection.Dispose();
|
return dataSet;
|
}
|
}
|
|
|
|
|
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
|
{
|
string strCon = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
|
using (SqlConnection connection = new SqlConnection(strCon))
|
{
|
DataSet dataSet = new DataSet();
|
connection.Open();
|
SqlDataAdapter adapter = new SqlDataAdapter();
|
adapter.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
|
adapter.SelectCommand.CommandTimeout = Times;
|
adapter.Fill(dataSet, tableName);
|
connection.Close();
|
connection.Dispose();
|
return dataSet;
|
}
|
}
|
|
|
|
public static int ExecuteSql(string SQLString)
|
{
|
int num2;
|
string strCon = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
|
using (SqlConnection connection = new SqlConnection(strCon))
|
{
|
SqlCommand command = new SqlCommand(SQLString, connection);
|
try
|
{
|
connection.Open();
|
num2 = command.ExecuteNonQuery();
|
connection.Close();
|
return num2;
|
}
|
catch (SqlException exception)
|
{
|
connection.Close();
|
throw new Exception(exception.Message);
|
}
|
finally
|
{
|
connection.Close();
|
if (command != null)
|
{
|
command.Dispose();
|
}
|
}
|
}
|
return num2;
|
}
|
|
public static SqlTransaction GetSqlTransaction()
|
{
|
string strCon = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
|
using (SqlConnection connection = new SqlConnection(strCon))
|
{
|
connection.Open();
|
//SqlCommand command = new SqlCommand();
|
//command.Connection = connection;
|
SqlTransaction transaction = connection.BeginTransaction();
|
return transaction;
|
}
|
}
|
|
/// <summary>
|
/// ÊÂÎï
|
/// </summary>
|
/// <param name="SQLStringList"></param>
|
public static bool ExecuteSqlTran(ArrayList SQLStringList)
|
{
|
string strCon = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
|
using (SqlConnection connection = new SqlConnection(strCon))
|
{
|
connection.Open();
|
SqlCommand command = new SqlCommand();
|
command.Connection = connection;
|
SqlTransaction transaction = connection.BeginTransaction();
|
command.Transaction = transaction;
|
try
|
{
|
for (int i = 0; i < SQLStringList.Count; i++)
|
{
|
string str = SQLStringList[i].ToString();
|
if (str.Trim().Length > 1)
|
{
|
command.CommandText = str;
|
command.ExecuteNonQuery();
|
}
|
}
|
transaction.Commit();
|
return true;
|
}
|
catch (SqlException exception)
|
{
|
transaction.Rollback();
|
return false;
|
throw new Exception(exception.Message);
|
|
}
|
}
|
}
|
|
|
public static object GetSingle(string SQLString)
|
{
|
object obj3;
|
string strCon = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");
|
using (SqlConnection connection = new SqlConnection(strCon))
|
{
|
SqlCommand command = new SqlCommand(SQLString, connection);
|
try
|
{
|
connection.Open();
|
object objA = command.ExecuteScalar();
|
if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
|
{
|
return null;
|
}
|
return objA;
|
}
|
catch (SqlException exception)
|
{
|
connection.Close();
|
throw new Exception(exception.Message);
|
}
|
finally
|
{
|
if (command != null)
|
{
|
command.Dispose();
|
}
|
}
|
}
|
return obj3;
|
}
|
|
|
|
|
|
}
|
}
|
