public DataSet ExecuteCommand(string Text, CommandType CmdType)
{
dsResult = new DataSet();
try
{
objCmd.CommandType = CmdType;
objCmd.CommandText = Text;
objCmd.Connection = objConn;
//if (Parameters != null && Parameters.Count > 0)
//{
// objCmd.Parameters.Add(Parameters);
//}
objDA = new SqlDataAdapter(objCmd);
objConn.Open();
objDA.Fill(dsResult);
objConn.Close();
if (Parameters != null)
{
Parameters.Clear();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
return(dsResult);
}
public void LimpiaParametros()
{
if (parametros != null)
{
parametros.Clear();
}
}
private void CreateParameters(SqlCommand r_objCommand, string r_strError)
{
if ((r_objCommand != null) && (!r_strError.Equals("")))
{
string[] strColumns;
SqlParameterCollection objParameters = r_objCommand.Parameters;
strColumns = r_strError.Split(COLUMN_SEPARATOR.ToCharArray());
objParameters.Clear();
objParameters.Add(new SqlParameter("@r_dtTraceDateTime",
SqlDbType.DateTime,
8));
objParameters.Add(new SqlParameter("@r_vcTraceCategory",
SqlDbType.VarChar,
50));
objParameters.Add(new SqlParameter("@r_vcTraceDescription",
SqlDbType.VarChar,
1024));
objParameters.Add(new SqlParameter("@r_vcStackTrace",
SqlDbType.VarChar,
2048));
objParameters.Add(new SqlParameter("@r_vcDetailedErrorDescription",
SqlDbType.VarChar,
2048));
int iCount = strColumns.GetLength(0);
for (int i = 0; i < iCount; i++)
{
objParameters[i].IsNullable = true;
objParameters[i].Direction = ParameterDirection.Input;
objParameters[i].Value = strColumns.GetValue(i).ToString().Trim();
}
}
}
public Object ExecuteScalar(String command, CommandType commandType, SqlParameterCollection Parameters, SqlTransaction trans)
{
if ((i_cn == null) || (i_cn.State == ConnectionState.Closed))
{
closeDB();
openDB();
}
Int16 step = 0;
while ((step < 10) && (conn.State == ConnectionState.Connecting))
{
System.Threading.Thread.Sleep(100);
}
SqlCommand cmd = new SqlCommand(command, i_cn);
cmd.CommandType = commandType;
cmd.CommandTimeout = i_timeout;
try
{
if (Parameters != null)
{
cmd.Parameters.Clear();
foreach (SqlParameter par in Parameters)
{
cmd.Parameters.Add(par.ParameterName, par.SqlDbType, par.Size).Value = par.Value;
}
}
if (trans != null)
{
cmd.Transaction = trans;
}
return(cmd.ExecuteScalar());
}
catch (Exception ex)
{
i_lastError = ex.Message;
throw ex;
}
finally
{
if (Parameters != null)
{
Parameters.Clear();
}
Parameters = null;
if (cmd != null)
{
cmd.Dispose();
}
cmd = null;
}
}
public void Insert2(String insertSQL, SqlParameterCollection Parameters)
{
if ((i_cn == null) || (i_cn.State == ConnectionState.Closed))
{
closeDB();
openDB();
}
Int16 step = 0;
while ((step < 10) && (conn.State == ConnectionState.Connecting))
{
System.Threading.Thread.Sleep(100);
}
SqlCommand cmd = new SqlCommand(insertSQL, i_cn);
SqlDataReader dr = null;
try
{
if (Parameters != null)
{
cmd.Parameters.Clear();
foreach (SqlParameter par in Parameters)
{
cmd.Parameters.Add(par.ParameterName, par.SqlDbType, par.Size).Value = par.Value;
}
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
i_lastError = ex.Message;
throw ex;
}
finally
{
if (Parameters != null)
{
Parameters.Clear();
}
Parameters = null;
if (cmd != null)
{
cmd.Dispose();
}
if (dr != null)
{
dr.Close();
}
cmd = null;
}
}
/// <summary>
/// 资质名称树结构
/// </summary>
/// <param name="searchCondition"></param>
/// <returns></returns>
public string getZTreeOfZzmc()
{
string sql = @"select codetype,CODE,codeinfo from uepp_code where codetype=@codetype and CODE in ('10','11','12','20') order by CODE ";
SqlParameterCollection spc = this.DB.CreateSqlParameterCollection();
spc.Add("@codetype", "企业资质序列");
DataTable dtZzxl = DB.ExeSqlForDataTable(sql, spc, "t");
spc.Clear();
string sql2 = @"select codetype,code,codeinfo,parentcode from uepp_code where parentcodetype=@parentcodetype and codetype=@codetype and parentcode in ('10','11','12','20') order by parentcode, orderid";
spc.Add("@parentcodetype", "企业资质序列");
spc.Add("@codetype", "企业资质类别");
DataTable dtZzlb = DB.ExeSqlForDataTable(sql2, spc, "t2");
DataRow[] zzxlRows = dtZzxl.Select("1=1");
string resultJosn = string.Empty;
if (zzxlRows.Length > 0)
{
resultJosn = @"[";
for (int i = 0; i < dtZzxl.Rows.Count; i++)
{
if (i == 0)
{
resultJosn += @"{""id"":" + dtZzxl.Rows[i]["code"] + @",""name"":""" + dtZzxl.Rows[i]["codeinfo"] + @""",""open"":""false"",""isParent"":true";
}
else
{
resultJosn += @",{""id"":" + dtZzxl.Rows[i]["code"] + @",""name"":""" + dtZzxl.Rows[i]["codeinfo"] + @""",""open"":""false"",""isParent"":true";
}
DataTable tempdtZzlb = dtZzlb.Select("parentcode=" + dtZzxl.Rows[i]["code"]).CopyToDataTable();
if (tempdtZzlb.Rows.Count > 0)
{
resultJosn += @",""children"":[";
for (int j = 0; j < tempdtZzlb.Rows.Count; j++)
{
if (j == 0)
{
resultJosn += @"{""id"":" + tempdtZzlb.Rows[j]["code"] + @",""name"":""" + tempdtZzlb.Rows[j]["codeinfo"] + @""",""isLeaf"":true }";
}
else
{
resultJosn += @",{""id"":" + tempdtZzlb.Rows[j]["code"] + @",""name"":""" + tempdtZzlb.Rows[j]["codeinfo"] + @""",""isLeaf"":true }";
}
}
resultJosn += "]";
}
resultJosn += "}";
}
resultJosn += "]";
}
return(resultJosn);
}
private void AttachParameters()
{
// Attach the parms to the command object
try
{
foreach (SqlParameter parm in _sqlParameters)
{
_sqlCommand.Parameters.Add(parm.ParameterName, parm.SqlDbType);
_sqlCommand.Parameters[parm.ParameterName].Value = parm.Value;
}
// Clear the parms after they have been added to the command object
_sqlParameters.Clear();
_sqlParameters = new SqlCommand().Parameters;
}
catch (Exception ex)
{
SetErrorMessage("AttachParameters - " + ex.Message);
}
}
public void LimparParametros()
{
try
{
objparametros.Clear();
}
catch (Exception erro)
{
throw new Exception("Erro ao Limpar paramêtro. Messagem:" + erro.Message);
}
}
/// <summary>
/// 执行参数化事务语句
/// </summary>
/// <param name="sqlDirectionary"></param>
/// <returns></returns>
public int ExecuteTransSql(Dictionary <String, List <CustomSqlParam> > sqlDirectionary)
{
SqlConnection conn = null;
SqlTransaction trans = null;
int status = 1;
try
{
DateTime dtnow = DateTime.Now;
using (DataAccess Access = new DataAccess())
{
Access.SetSQLLinkParam(_clk.m_ServerIP, _clk.m_DbName, _clk.m_ServerID, _clk.m_PassWord);
conn = Access.Connection(true);
trans = conn.BeginTransaction();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.Transaction = trans;
command.CommandType = CommandType.Text;
command.CommandTimeout = 200;
foreach (var item in sqlDirectionary)
{
string sqlstr = item.Key;
command.CommandText = sqlstr;
SqlParameterCollection sqlParams = command.Parameters;
sqlParams.Clear();
foreach (var sqlParam in item.Value)
{
sqlParams.Add(sqlParam.Name, sqlParam.Type);
sqlParams[sqlParam.Name].Value = sqlParam.Value;
}
int count = command.ExecuteNonQuery();
}
trans.Commit();
}
}
catch (Exception ex)
{
trans.Rollback();
status = 0;
}
trans.Dispose();
conn.Close();
return(status);
}
private StringBuilder DoAddParameters(SqlParameterCollection parameters, TableAdapterContext ctx, IEnumerable <string> paramColumns)
{
parameters.Clear();
if (paramColumns == null)
{
return(null);
}
StringBuilder sqlParamText = new StringBuilder();
TableSchema tblSchema = ctx.TableSchema;
int counter = 0;
foreach (string colName in paramColumns)
{
DataRow dtRow = tblSchema.ColumnDef(colName);
AddParameter(dtRow, ref parameters, ref sqlParamText, counter, ctx.KeyColumns);
counter++;
}
return((counter > 0)? sqlParamText: null);
}
//limpar parametros
public void limparParamentros()
{
sqlParametrosColecao.Clear();
}
public void LimparParametros()//Método para limpar Parametros
{
ColeccaoParametros.Clear();
}
//Remove todos os elementos contidos na lista de parâmetros de entrada.
public void LimparParametros()
{
//Método Clear() remove todos os elementos da lista.
listaParametrosSql.Clear();
}
//METODO PARA LIMPAR COLEÇÃO DE PARAMETRO ANDE DE ADICIONA NOVOS
public void limparSqlParameterCollection()
{
sqlParameterCollection.Clear();
}
/// <summary>
/// Método que limpa a coleção de parametros.
/// </summary>
private void ClearParameterCollection()
{
//Limpa a coleção de parametros.
parameterCollection.Clear();
}
/// <summary>
/// Metodo que remove todos parametros da coleção
/// </summary>
public void limpaParametros()
{
sqlParametros.Clear();
}
public static void LimparParametros()
{
objParametros.Clear();
}
public void CleanParameter()
{
parameterCollection.Clear();
}
//Remove todos os elementos contidos na lista de parâmetros de entrada.
public void LimparParametros()
{
listaParametrosSql.Clear();
}
public void limpaparametros()
{
_sql.Clear();
}
// Método para limpar os parâmetros da SqlParameterCollection criada.
public void LimparParametros()
{
colecaoDeParametrosSql.Clear();
}
protected void ClearParameters()
{
_sqlParameterCollection.Clear();
}
/// <summary>
/// 事务执行sql后批量插入
/// </summary>
/// <param name="sqlDirectionary"></param>
/// <param name="model"></param>
/// <returns></returns>
public int ExecuteThenInsertTrans(Dictionary <String, List <CustomSqlParam> > sqlDirectionary, DataSet model)
{
SqlConnection conn = null;
SqlTransaction trans = null;
SqlBulkCopy sqlBulk = null;
int status = 1;
try
{
DateTime dtnow = DateTime.Now;
using (DataAccess Access = new DataAccess())
{
Access.SetSQLLinkParam(_clk.m_ServerIP, _clk.m_DbName, _clk.m_ServerID, _clk.m_PassWord);
conn = Access.Connection(true);
trans = conn.BeginTransaction();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.Transaction = trans;
command.CommandType = CommandType.Text;
command.CommandTimeout = 200;
foreach (var item in sqlDirectionary)
{
string sqlstr = item.Key;
command.CommandText = sqlstr;
SqlParameterCollection sqlParams = command.Parameters;
sqlParams.Clear();
foreach (var sqlParam in item.Value)
{
sqlParams.Add(sqlParam.Name, sqlParam.Type);
sqlParams[sqlParam.Name].Value = sqlParam.Value;
}
int count = command.ExecuteNonQuery();
}
///最终执行插入
sqlBulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans);
sqlBulk.BulkCopyTimeout = 5000;
sqlBulk.DestinationTableName = model.Tables[0].TableName;
sqlBulk.WriteToServer(model.Tables[0]);
trans.Commit();
}
}
catch (Exception ex)
{
trans.Rollback();
status = 0;
}
finally
{
trans.Dispose();
sqlBulk.Close();
conn.Close();
}
return(status);
}
public void ClearParameterCollection()
{
ParameterCollection.Clear();
}
public void ExecuteNonQuery(String command, CommandType commandType, SqlParameterCollection Parameters, SqlTransaction trans)
{
if ((i_cn == null) || (i_cn.State == ConnectionState.Closed))
{
closeDB();
openDB();
}
Int16 step = 0;
while ((step < 10) && (conn.State == ConnectionState.Connecting))
{
System.Threading.Thread.Sleep(100);
}
SqlCommand cmd = new SqlCommand(command, i_cn);
cmd.CommandType = commandType;
cmd.CommandTimeout = i_timeout;
String debug = "";
try
{
if (Parameters != null)
{
cmd.Parameters.Clear();
foreach (SqlParameter par in Parameters)
{
debug += "DECLARE " + par.ParameterName + " " + par.SqlDbType.ToString();
if (par.Size > 0)
{
debug += "(" + par.Size + ")";
}
debug += Environment.NewLine;
debug += "SET " + par.ParameterName + " = " + par.Value + Environment.NewLine;
cmd.Parameters.Add(par.ParameterName, par.SqlDbType, par.Size).Value = par.Value;
}
}
if (trans != null)
{
cmd.Transaction = trans;
}
DebugLog(PluginLogType.Debug, "ExecuteNonQuery.SQL = " + cmd.CommandText);
DebugLog(PluginLogType.Debug, "ExecuteNonQuery.Parameters " + cmd.Parameters.Count);
foreach (SqlParameter p in cmd.Parameters)
{
DebugLog(PluginLogType.Debug, "ExecuteNonQuery.Parameters[" + p.ParameterName + "] = " + p.Value);
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
i_lastError = ex.Message;
throw ex;
}
finally
{
if (Parameters != null)
{
Parameters.Clear();
}
Parameters = null;
if (cmd != null)
{
cmd.Dispose();
}
cmd = null;
}
}
public void limparParametros()
{
sqlPC.Clear();
}
public void LimparParametros()
{
Colecao.Clear();
}
private void LimparParametros()
{
sqlParameterCollection.Clear();
}
//Limpar Parâmetros;
public void LimparParametros()
{
sqlParameterCollection.Clear();
}
protected void LimparParametro()
{
sqlParameterCollection.Clear();
}