using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Web;
|
using System.Data.SqlClient;
|
using System.Data;
|
using System.Collections;
|
using VueWebApi.Tools;
|
|
namespace VueWebApi.Tools
|
{
|
public class DataOperator : IDisposable
|
{
|
private static string connectionString = string.Empty;
|
|
private static string connectionName = string.Empty;
|
|
private static string key = "fengyi";
|
|
private static string iv = "fengyibi";
|
|
private SqlConnection _conn = null;
|
|
private static string uid;
|
|
public static string password;
|
|
private SqlCommand _cmd = null;
|
|
private SqlDataAdapter _adp = null;
|
|
public static string ConnectionName
|
{
|
get
|
{
|
return DataOperator.connectionName;
|
}
|
}
|
|
public static string Key
|
{
|
get
|
{
|
return DataOperator.key;
|
}
|
set
|
{
|
DataOperator.key = value;
|
}
|
}
|
|
public static string IV
|
{
|
get
|
{
|
return DataOperator.iv;
|
}
|
set
|
{
|
DataOperator.iv = value;
|
}
|
}
|
|
public SqlConnection Connection
|
{
|
get
|
{
|
return this._conn;
|
}
|
}
|
|
public string UId
|
{
|
get
|
{
|
return DataOperator.uid;
|
}
|
}
|
|
public string Password
|
{
|
get
|
{
|
return DataOperator.password;
|
}
|
}
|
|
public DataOperator()
|
{
|
if (DataOperator.connectionString == "")
|
{
|
throw new ArgumentNullException("ConnectionString");
|
}
|
}
|
|
public DataOperator(string server, string puid, string pwd, string dbname)
|
{
|
try
|
{
|
if (server == null || server.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("Type:string pramName:server", "数据库服务器为空！");
|
}
|
if (DataOperator.uid == null || DataOperator.uid.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("Type:string pramName:uid", "数据库用户名为空！");
|
}
|
if (dbname == null || dbname.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("Type:string pramName:dbname", "数据库名为空！");
|
}
|
DataOperator.uid = puid;
|
DataOperator.password = pwd;
|
string text = string.Format("server={0};uid={1};pwd={2};database={3};", new object[]
|
{
|
server,
|
DataOperator.uid,
|
pwd,
|
dbname
|
});
|
this._conn = new SqlConnection(text);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
public bool getTable(string sqlstr, ref DataTable rdt)
|
{
|
bool result;
|
try
|
{
|
if (null == rdt)
|
{
|
rdt = new DataTable();
|
}
|
else
|
{
|
rdt.Clear();
|
}
|
if (null == this._conn)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._adp = new SqlDataAdapter(sqlstr, this._conn);
|
this._adp.Fill(rdt);
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
}
|
return result;
|
}
|
|
public DataTable getTable(string sqlstr)
|
{
|
DataTable result;
|
try
|
{
|
DataTable dataTable = new DataTable();
|
if (null == this._conn)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._adp = new SqlDataAdapter(sqlstr, this._conn);
|
this._adp.Fill(dataTable);
|
result = dataTable;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
}
|
return result;
|
}
|
|
public DataTable GetTable(string sqlstr)
|
{
|
DataTable result;
|
try
|
{
|
DataTable dataTable = new DataTable();
|
if (null == this._conn)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._adp = new SqlDataAdapter(sqlstr, this._conn);
|
this._adp.Fill(dataTable);
|
result = dataTable;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
}
|
return result;
|
}
|
|
public bool getTable(string sqlstr, SqlParameter[] param, ref DataTable rdt)
|
{
|
bool result;
|
try
|
{
|
if (null == rdt)
|
{
|
rdt = new DataTable();
|
}
|
else
|
{
|
rdt.Clear();
|
}
|
if (null == this._conn)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._adp = new SqlDataAdapter(sqlstr, this._conn);
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._adp.SelectCommand.Parameters.Add((SqlParameter)((ICloneable)param[i]).Clone());
|
}
|
this._adp.Fill(rdt);
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
}
|
return result;
|
}
|
|
public DataTable GetTable(string sqlstr, SqlParameter[] param)
|
{
|
DataTable result;
|
try
|
{
|
DataTable dataTable = new DataTable();
|
if (null == this._conn)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._adp = new SqlDataAdapter(sqlstr, this._conn);
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._adp.SelectCommand.Parameters.Add((SqlParameter)((ICloneable)param[i]).Clone());
|
}
|
this._adp.Fill(dataTable);
|
result = dataTable;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
}
|
return result;
|
}
|
|
public DataTable getTable(string sqlstr, SqlParameter[] param)
|
{
|
DataTable result;
|
try
|
{
|
DataTable dataTable = new DataTable();
|
if (null == this._conn)
|
{
|
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._adp = new SqlDataAdapter(sqlstr, this._conn);
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._adp.SelectCommand.Parameters.Add((SqlParameter)((ICloneable)param[i]).Clone());
|
}
|
this._adp.Fill(dataTable);
|
result = dataTable;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
}
|
|
return result;
|
}
|
|
public DataSet getdate(string sqlstr, SqlParameter[] param)
|
{
|
DataSet result;
|
try
|
{
|
DataSet dset = new DataSet();
|
if (null == this._conn)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._adp = new SqlDataAdapter(sqlstr, this._conn);
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._adp.SelectCommand.Parameters.Add((SqlParameter)((ICloneable)param[i]).Clone());
|
}
|
this._adp.Fill(dset);
|
result = dset;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
}
|
return result;
|
}
|
|
public object getObject(string sqlstr)
|
{
|
object result;
|
try
|
{
|
if (sqlstr == null || sqlstr.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("sqlstr", "SQL语句不能为空！");
|
}
|
if (null == this._conn)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._cmd = new SqlCommand(sqlstr, this._conn);
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
result = this._cmd.ExecuteScalar();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._cmd)
|
{
|
this._cmd.Dispose();
|
this._cmd = null;
|
}
|
}
|
return result;
|
}
|
|
public object getObject(string sqlstr, SqlParameter[] param)
|
{
|
object result;
|
try
|
{
|
if (sqlstr == null || sqlstr.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("sqlstr", "SQL语句不能为空！");
|
}
|
if (null == this._conn)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._cmd = new SqlCommand(sqlstr, this._conn);
|
this._cmd.Parameters.Clear();
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._cmd.Parameters.Add((SqlParameter)((ICloneable)param[i]).Clone());
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
result = this._cmd.ExecuteScalar();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._cmd)
|
{
|
this._cmd.Dispose();
|
this._cmd = null;
|
}
|
}
|
return result;
|
}
|
|
public int getCount(string sqlstr)
|
{
|
int result;
|
try
|
{
|
if (sqlstr == null || sqlstr.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("sqlstr", "SQL语句不能为空！");
|
}
|
if (null == this._conn)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd = new SqlCommand(sqlstr, this._conn);
|
object obj = this._cmd.ExecuteScalar();
|
if (null == obj)
|
{
|
throw new ArgumentNullException("_cmd.ExecuteScalar()", "结果返回空！");
|
}
|
result = Convert.ToInt32(obj);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._cmd)
|
{
|
this._cmd.Dispose();
|
this._cmd = null;
|
}
|
}
|
return result;
|
}
|
|
public int getCount(string sqlstr, SqlParameter[] param)
|
{
|
int result;
|
try
|
{
|
if (sqlstr == null || sqlstr.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("sqlstr", "SQL语句不能为空！");
|
}
|
if (null == this._conn)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd = new SqlCommand(sqlstr, this._conn);
|
this._cmd.Parameters.Clear();
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._cmd.Parameters.Add((SqlParameter)((ICloneable)param[i]).Clone());
|
}
|
object obj = this._cmd.ExecuteScalar();
|
if (null == obj)
|
{
|
throw new ArgumentNullException("_cmd.ExecuteScalar()", "结果返回空！");
|
}
|
result = Convert.ToInt32(obj);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._cmd)
|
{
|
this._cmd.Dispose();
|
this._cmd = null;
|
}
|
}
|
return result;
|
}
|
|
public bool executeSql(string sqlstr)
|
{
|
bool result;
|
try
|
{
|
if (sqlstr == null || sqlstr.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("sqlstr", "SQL语句为空！");
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._cmd = new SqlCommand(sqlstr, this._conn);
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd.ExecuteNonQuery();
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (this._cmd != null)
|
{
|
this._cmd.Dispose();
|
this._cmd = null;
|
}
|
}
|
return result;
|
}
|
|
public bool executeSql(string sqlstr, SqlParameter[] param)
|
{
|
bool result;
|
try
|
{
|
if (sqlstr == null || sqlstr.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("sqlstr", "SQL语句为空！");
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._cmd = new SqlCommand(sqlstr, this._conn);
|
this._cmd.Parameters.Clear();
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._cmd.Parameters.Add((SqlParameter)((ICloneable)param[i]).Clone());
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd.ExecuteNonQuery();
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (this._cmd != null)
|
{
|
this._cmd.Dispose();
|
this._cmd = null;
|
}
|
}
|
return result;
|
}
|
|
public bool executeSql(List<string> list)
|
{
|
string[] array = new string[list.Count];
|
for (int i = 0; i < list.Count; i++)
|
{
|
array[i] = list[i];
|
}
|
return this.executeSql(array);
|
}
|
|
public bool executeSql(string[] list)
|
{
|
SqlTransaction sqlTransaction = null;
|
bool result;
|
try
|
{
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
sqlTransaction = this._conn.BeginTransaction();
|
for (int i = 0; i < list.Length; i++)
|
{
|
string text = list[i];
|
if (text == null || text.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("sqlstr", "SQL语句为空！");
|
}
|
this._cmd = new SqlCommand(text, this._conn);
|
this._cmd.Transaction = sqlTransaction;
|
this._cmd.ExecuteNonQuery();
|
}
|
sqlTransaction.Commit();
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
if (null != sqlTransaction)
|
{
|
sqlTransaction.Rollback();
|
}
|
throw ex;
|
}
|
finally
|
{
|
if (this._cmd != null)
|
{
|
this._cmd.Dispose();
|
this._cmd = null;
|
}
|
if (null != sqlTransaction)
|
{
|
sqlTransaction.Dispose();
|
sqlTransaction = null;
|
}
|
}
|
return result;
|
}
|
|
//public bool executeSql(SQLExecEntity entity)
|
//{
|
// return this.executeSql(new List<SQLExecEntity>
|
// {
|
// entity
|
// });
|
//}
|
|
//public bool executeSql(List<SQLExecEntity> list)
|
//{
|
// SqlTransaction sqlTransaction = null;
|
// bool result;
|
// try
|
// {
|
// if (this._conn == null)
|
// {
|
// this._conn = new SqlConnection(DataOperator.connectionString);
|
// }
|
// if (ConnectionState.Closed == this._conn.State)
|
// {
|
// this._conn.Open();
|
// }
|
// sqlTransaction = this._conn.BeginTransaction();
|
// foreach (SQLExecEntity current in list)
|
// {
|
// this._cmd = new SqlCommand(current.SqlStr, this._conn);
|
// SqlParameter[] @params = new ParamCollections(current.Param).GetParams();
|
// SqlParameter[] array = @params;
|
// for (int i = 0; i < array.Length; i++)
|
// {
|
// SqlParameter value = array[i];
|
// this._cmd.Parameters.Add(value);
|
// }
|
// this._cmd.Transaction = sqlTransaction;
|
// this._cmd.ExecuteNonQuery();
|
// }
|
// sqlTransaction.Commit();
|
// result = true;
|
// }
|
// catch (Exception ex)
|
// {
|
// if (null != sqlTransaction)
|
// {
|
// sqlTransaction.Rollback();
|
// }
|
// throw ex;
|
// }
|
// finally
|
// {
|
// if (this._cmd != null)
|
// {
|
// this._cmd.Dispose();
|
// this._cmd = null;
|
// }
|
// if (null != sqlTransaction)
|
// {
|
// sqlTransaction = null;
|
// }
|
// }
|
// return result;
|
//}
|
|
public bool executeProduct(string sqlstr, ref ArrayList outAl)
|
{
|
DataTable dataTable = new DataTable();
|
bool result;
|
try
|
{
|
if (sqlstr == null || sqlstr.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("sqlstr", "SQL不能为空！");
|
}
|
if (null != outAl)
|
{
|
outAl.Clear();
|
}
|
else
|
{
|
outAl = new ArrayList();
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
this._adp = new SqlDataAdapter(sqlstr, this._conn);
|
this._adp.Fill(dataTable);
|
if (null != dataTable)
|
{
|
for (int i = 0; i < dataTable.Columns.Count; i++)
|
{
|
if (null != dataTable.Rows[0][i])
|
{
|
outAl.Add(dataTable.Rows[0][i]);
|
}
|
}
|
}
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
if (null != dataTable)
|
{
|
dataTable.Clear();
|
dataTable.Dispose();
|
dataTable = null;
|
}
|
}
|
return result;
|
}
|
|
public ArrayList executeProduct(string produreName, SqlParameter[] param, bool returnDt)
|
{
|
DataTable dataTable = new DataTable();
|
ArrayList result;
|
try
|
{
|
ArrayList arrayList = new ArrayList();
|
if (produreName == null || produreName.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("produreName", "过程名不能为空！");
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd = new SqlCommand();
|
this._cmd.Connection = this._conn;
|
this._cmd.CommandType = CommandType.StoredProcedure;
|
this._cmd.CommandText = produreName;
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._cmd.Parameters.Add(param[i]);
|
}
|
if (returnDt)
|
{
|
SqlDataReader sqlDataReader = this._cmd.ExecuteReader();
|
for (int i = 0; i < sqlDataReader.FieldCount; i++)
|
{
|
dataTable.Columns.Add(sqlDataReader.GetName(i), sqlDataReader.GetFieldType(i));
|
}
|
while (sqlDataReader.Read())
|
{
|
DataRow dataRow = dataTable.NewRow();
|
dataRow.BeginEdit();
|
for (int i = 0; i < sqlDataReader.FieldCount; i++)
|
{
|
dataRow[i] = sqlDataReader.GetValue(i);
|
}
|
dataRow.EndEdit();
|
dataTable.Rows.Add(dataRow);
|
}
|
sqlDataReader.Close();
|
arrayList.Add(dataTable);
|
}
|
else
|
{
|
object value = this._cmd.ExecuteScalar();
|
arrayList.Add(value);
|
}
|
result = arrayList;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return result;
|
}
|
|
public DataTable ExecuteProductData(string produreName, SqlParameter[] param)
|
{
|
DataTable result;
|
try
|
{
|
if (produreName == null || produreName.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("produreName", "过程名不能为空！");
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd = new SqlCommand();
|
this._cmd.Connection = this._conn;
|
this._cmd.CommandType = CommandType.StoredProcedure;
|
this._cmd.CommandText = produreName;
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._cmd.Parameters.Add(param[i]);
|
}
|
DataTable dataTable = new DataTable();
|
this._adp = new SqlDataAdapter(this._cmd);
|
this._adp.Fill(dataTable);
|
result = dataTable;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
}
|
return result;
|
}
|
|
public List<DataTable> ExecuteProductDataList(string produreName, SqlParameter[] param)
|
{
|
List<DataTable> result =new List<DataTable>();
|
try
|
{
|
if (produreName == null || produreName.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("produreName", "过程名不能为空！");
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd = new SqlCommand();
|
this._cmd.Connection = this._conn;
|
this._cmd.CommandType = CommandType.StoredProcedure;
|
this._cmd.CommandText = produreName;
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._cmd.Parameters.Add(param[i]);
|
}
|
DataSet ds = new DataSet();
|
this._adp = new SqlDataAdapter(this._cmd);
|
this._adp.Fill(ds);
|
for (int i = 0; i < ds.Tables.Count; i++)
|
{
|
result.Add(ds.Tables[i]);
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
}
|
return result;
|
}
|
|
public DataSet ExecuteProductDataSet(string produreName, SqlParameter[] param)
|
{
|
DataSet result;
|
try
|
{
|
if (produreName == null || produreName.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("produreName", "过程名不能为空！");
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd = new SqlCommand();
|
this._cmd.Connection = this._conn;
|
this._cmd.CommandType = CommandType.StoredProcedure;
|
this._cmd.CommandText = produreName;
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._cmd.Parameters.Add(param[i]);
|
}
|
DataSet dataSet = new DataSet();
|
this._adp = new SqlDataAdapter(this._cmd);
|
this._adp.Fill(dataSet);
|
result = dataSet;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
}
|
return result;
|
}
|
|
public bool BatchUpdate(DataTable dt, string sqlStr, SqlParameter[] param)
|
{
|
return this.BatchUpdate(dt, sqlStr, param, 100, false);
|
}
|
|
public bool BatchUpdate(DataTable dt, string sqlStr, SqlParameter[] param, int batchSize, bool updateAllRows)
|
{
|
bool result;
|
try
|
{
|
if (updateAllRows)
|
{
|
dt.AcceptChanges();
|
foreach (DataRow dataRow in dt.Rows)
|
{
|
dataRow.SetModified();
|
}
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._adp = new SqlDataAdapter();
|
this._adp.UpdateCommand = new SqlCommand(sqlStr, this._conn);
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._adp.UpdateCommand.Parameters.Add(param[i]);
|
}
|
this._adp.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
|
this._adp.UpdateBatchSize = batchSize;
|
this._adp.Update(dt);
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
this._adp = null;
|
}
|
if (null != this._conn)
|
{
|
this._conn.Close();
|
this._conn = null;
|
}
|
}
|
return result;
|
}
|
public Hashtable executeProduct(string produreName, SqlParameter[] param)
|
{
|
Hashtable result;
|
try
|
{
|
Hashtable hashtable = new Hashtable();
|
if (produreName == null || produreName.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("produreName", "过程名不能为空！");
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd = new SqlCommand();
|
this._cmd.Connection = this._conn;
|
this._cmd.CommandType = CommandType.StoredProcedure;
|
this._cmd.CommandText = produreName;
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._cmd.Parameters.Add((SqlParameter)((ICloneable)param[i]).Clone());
|
}
|
this._cmd.ExecuteNonQuery();
|
for (int i = 0; i < this._cmd.Parameters.Count; i++)
|
{
|
if (this._cmd.Parameters[i].Direction == ParameterDirection.Output || this._cmd.Parameters[i].Direction == ParameterDirection.InputOutput)
|
{
|
hashtable.Add(this._cmd.Parameters[i].SourceColumn, this._cmd.Parameters[i].Value);
|
}
|
}
|
result = hashtable;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return result;
|
}
|
|
public bool ExecuteProduct(string produreName, SqlParameter[] param)
|
{
|
bool result;
|
try
|
{
|
if (produreName == null || produreName.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("produreName", "过程名不能为空！");
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd = new SqlCommand();
|
this._cmd.Connection = this._conn;
|
this._cmd.CommandType = CommandType.StoredProcedure;
|
this._cmd.CommandText = produreName;
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._cmd.Parameters.Add(param[i]);
|
}
|
this._cmd.ExecuteNonQuery();
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return result;
|
}
|
|
public string ExecuteDBProduct(string produreName, SqlParameter[] param)
|
{
|
string result;
|
try
|
{
|
if (produreName == null || produreName.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("produreName", "过程名不能为空！");
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
this._cmd = new SqlCommand();
|
this._cmd.Connection = this._conn;
|
this._cmd.CommandType = CommandType.StoredProcedure;
|
this._cmd.CommandText = produreName;
|
for (int i = 0; i < param.Length; i++)
|
{
|
this._cmd.Parameters.Add(param[i]);
|
}
|
this._cmd.ExecuteNonQuery();
|
result = (string)this._cmd.Parameters["@ResultValues"].Value;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return result;
|
}
|
|
public static void SetConnectionName(string serverName)
|
{
|
DataOperator.SetConnectionName(serverName, DataOperator.key, DataOperator.iv);
|
}
|
|
public static void SetConnectionString(string conStr, string key, string iv)
|
{
|
if (conStr.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("conStr");
|
}
|
DataOperator.connectionString = Encrypt.DecryptStr(conStr, key, iv);
|
}
|
|
public static void SetConnectionString1(string conStr)
|
{
|
if (conStr.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("conStr");
|
}
|
DataOperator.connectionString = conStr;
|
}
|
|
public static void SetConnectionName(string serverName, string key, string iv)
|
{
|
if (serverName.Trim().Length == 0)
|
{
|
throw new ArgumentNullException("serverName");
|
}
|
DataOperator.connectionName = serverName.Trim();
|
DataOperator.connectionString = DataOperator.GetConnectionObjString(DataOperator.connectionName, "connectionString", key, iv);
|
DataOperator.uid = DataOperator.GetConnectionObjString(DataOperator.connectionName, "Uid", key, iv);
|
DataOperator.password = DataOperator.GetConnectionObjString(DataOperator.connectionName, "Password", key, iv);
|
}
|
|
private static string GetConnectionObjString(string serverName, string itemName)
|
{
|
return DataOperator.GetConnectionObjString(serverName, itemName, DataOperator.key, DataOperator.iv);
|
}
|
|
private static string GetConnectionObjString(string serverName, string itemName, string key, string iv)
|
{
|
return Encrypt.DecryptStr(Regedit.GetValue(serverName, itemName, false), key, iv);
|
}
|
|
public bool TestDboState()
|
{
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
bool result;
|
try
|
{
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
result = true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return result;
|
}
|
|
public string checkSql(string sqlstr)
|
{
|
sqlstr = sqlstr.Replace("--", "__").Replace("'", "‘").Replace(";", "；").Trim();
|
return sqlstr;
|
}
|
|
public void Close()
|
{
|
if (null != this._conn)
|
{
|
try
|
{
|
if (ConnectionState.Open == this._conn.State)
|
{
|
this._conn.Close();
|
}
|
}
|
catch (Exception)
|
{
|
}
|
finally
|
{
|
this._conn = null;
|
}
|
}
|
if (null != this._adp)
|
{
|
this._adp.Dispose();
|
}
|
this._adp = null;
|
if (null != this._cmd)
|
{
|
this._cmd.Dispose();
|
}
|
}
|
|
public void Dispose()
|
{
|
this.Close();
|
}
|
|
/// <summary>
|
/// 执行多条SQL语句，实现数据库事务。
|
/// </summary>
|
/// <param name="sql">多条SQL语句</param>
|
/// <returns>影响的记录数</returns>
|
public int ExecuteSqlTran(List<string> list, params SqlParameter[] param)
|
{
|
if (list == null || list.Count == 0)
|
{
|
throw new ArgumentNullException("sqlstr", "SQL语句为空！");
|
}
|
if (this._conn == null)
|
{
|
this._conn = new SqlConnection(DataOperator.connectionString);
|
}
|
for (int i = 0; i < list.Count; i++)
|
{
|
this._cmd = new SqlCommand(list[i], this._conn);
|
this._cmd.Parameters.Clear();
|
}
|
for (int j = 0; j < param.Length; j++)
|
{
|
this._cmd.Parameters.Add((SqlParameter)((ICloneable)param[j]).Clone());
|
}
|
if (ConnectionState.Closed == this._conn.State)
|
{
|
this._conn.Open();
|
}
|
//if (conn.State.ToString() == "OPEN")
|
//{
|
// conn.Close();
|
//}
|
//conn.Open();
|
//SqlCommand cmd = new SqlCommand();
|
this._cmd.Connection = this._conn;
|
SqlTransaction tx = this._conn.BeginTransaction();
|
this._cmd.Transaction = tx;
|
try
|
{
|
int count = 0;
|
for (int n = 0; n < list.Count; n++)
|
{
|
|
string strsql = list[n];
|
|
if (strsql.Trim().Length > 1)
|
{
|
|
this._cmd.CommandText = strsql;
|
count += this._cmd.ExecuteNonQuery();
|
}
|
|
}
|
|
tx.Commit();
|
this._conn.Close();
|
return count;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
tx.Rollback();
|
this._conn.Close();
|
return -1;
|
}
|
}
|
|
|
|
#region【批量写入sql】
|
//public int AddDBSqlBulkCopy(DataTable dt, string targetTable)
|
//{
|
|
|
// this._conn.Open();
|
// //声明SqlBulkCopy ,using释放非托管资源
|
|
// using (OracleBulkCopy sqlBC = new OracleBulkCopy(this._conn))
|
// {
|
|
// //一次批量的插入的数据量
|
|
// sqlBC.BatchSize = 1000;
|
|
// //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
|
|
// sqlBC.BulkCopyTimeout = 60;
|
|
// //設定NotifyAfter 属性，以便在每插入10000 条数据时，呼叫相应事件。
|
|
// sqlBC.NotifyAfter = 10000;
|
|
// sqlBC.OracleRowsCopied += new OracleRowsCopiedEventHandler(sqlBC_OracleRowsCopied);
|
|
// //设置要批量写入的表
|
|
// sqlBC.DestinationTableName = "dbo.Name";
|
|
// //自定义的datatable和数据库的字段进行对应
|
|
// sqlBC.ColumnMappings.Add("Name", "userName");
|
|
// // sqlBC.ColumnMappings.Add("Name",1);
|
|
// //批量写入
|
|
// sqlBC.WriteToServer(dt);
|
|
// }
|
|
// this._conn.Dispose();
|
|
//}
|
#endregion
|
}
|
}
|
