public static int ExecuteNonQuery(DatabaseEnum database, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
DbCommand cmd = Provider.CreateCommand();
using (DbConnection conn = Provider.CreateConnection())
{
conn.ConnectionString = ConnectionStringFactory.CreateConnectionString(database);
return(ExecuteNonQuery(conn, cmdType, cmdText, commandParameters));
}
}
public static object ExecuteDataObject(DatabaseEnum databaseName, CommandType commandType, string commandText, params SqlParameter[] parms)
{
DataSet ds = ExecuteDataset(ConnectionStringFactory.CreateConnectionString(databaseName), commandType, commandText, parms);
if (ds != null &&
ds.Tables[0] != null)
{
return(ds.Tables[0].Rows[0][0]);
}
return("");
}
public static DataSet ExecuteDataset(DatabaseEnum databaseName, string spName, params object[] parameterValues)
{
string connectionString = ConnectionStringFactory.CreateConnectionString(databaseName);
if ((parameterValues != null) && (parameterValues.Length > 0))
{
return(ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, (SqlParameter[])parameterValues));
}
else
{
return(ExecuteDataset(connectionString, CommandType.StoredProcedure, spName));
}
}
public static DataTable QueryLogDB(string sql)
{
try
{
DataSet dataset = new DataSet();
SqlConnection conn = new SqlConnection(ConnectionStringFactory.CreateConnectionString(DatabaseEnum.Log4Net_CMD));
SqlCommand command = conn.CreateCommand();
command.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dataset);
return(dataset.Tables[0]);
}
catch
{
return(null);
}
}
public static DataSet ExecuteDataset(DatabaseEnum databaseName, CommandType commandType, string commandText, params SqlParameter[] parames)
{
return(ExecuteDataset(ConnectionStringFactory.CreateConnectionString(databaseName), commandType, commandText, parames));
}
public static DataTable ExecuteTable(DatabaseEnum database, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
return(ExecuteTable(ConnectionStringFactory.CreateConnectionString(database), cmdType, cmdText, commandParameters));
}
private int InsertPostgres(DataTable table, bool isHandle, out string errMsg)
{
errMsg = "";
int count = 0;
string tableName = "tbl_Interface_ProcessLog";
if (isHandle)
{
tableName = "tbl_Interface_HandleLog";
}
NpgsqlConnection conn = new NpgsqlConnection(ConnectionStringFactory.CreateConnectionString(DatabaseEnum.PGLog4Net_CMD));
try
{
conn.Open();
DataSet dataSet = new DataSet();
NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(string.Format("SELECT * FROM {0} WHERE id = -1", tableName), ConnectionStringFactory.CreateConnectionString(DatabaseEnum.PGLog4Net_CMD));
dataAdapter.InsertCommand = new NpgsqlCommand(string.Format("INSERT INTO {0}(ikey, username, logtime, clientip, module, orderno, logtype, content, serverip, keyword) ", tableName) +
" values (:ikey, :username, :logtime, :clientip, :module, :orderno, :logtype, :content, :serverip, :keyword)", conn);
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("ikey", NpgsqlDbType.Varchar));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("username", NpgsqlDbType.Varchar));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("logtime", NpgsqlDbType.Timestamp));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("clientip", NpgsqlDbType.Varchar));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("module", NpgsqlDbType.Varchar));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("orderno", NpgsqlDbType.Varchar));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("logtype", NpgsqlDbType.Varchar));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("content", NpgsqlDbType.Varchar));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("serverip", NpgsqlDbType.Varchar));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("keyword", NpgsqlDbType.Varchar));
dataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[3].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[4].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[5].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[6].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[7].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[8].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[9].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[0].SourceColumn = "ikey";
dataAdapter.InsertCommand.Parameters[1].SourceColumn = "username";
dataAdapter.InsertCommand.Parameters[2].SourceColumn = "logtime";
dataAdapter.InsertCommand.Parameters[3].SourceColumn = "clientip";
dataAdapter.InsertCommand.Parameters[4].SourceColumn = "module";
dataAdapter.InsertCommand.Parameters[5].SourceColumn = "orderno";
dataAdapter.InsertCommand.Parameters[6].SourceColumn = "logtype";
dataAdapter.InsertCommand.Parameters[7].SourceColumn = "content";
dataAdapter.InsertCommand.Parameters[8].SourceColumn = "serverip";
dataAdapter.InsertCommand.Parameters[9].SourceColumn = "keyword";
dataAdapter.Fill(dataSet);
DataTable newTable = dataSet.Tables[0];
foreach (DataRow row in table.Rows)
{
DataRow newRow = newTable.NewRow();
newRow["ikey"] = row["ikey"];
newRow["username"] = row["username"];
newRow["logtime"] = row["logtime"];
newRow["clientip"] = row["clientip"];
newRow["serverip"] = row["serverip"];
newRow["module"] = row["module"];
newRow["keyword"] = row["keyword"];
newRow["orderno"] = row["orderno"];
newRow["logtype"] = row["logtype"];
newRow["content"] = row["content"];
newTable.Rows.Add(newRow);
}
DataSet ds2 = dataSet.GetChanges();
count = dataAdapter.Update(ds2);
dataSet.Merge(ds2);
dataSet.AcceptChanges();
}
catch (Exception ex)
{
errMsg = GetString(ex);
count = 0;
}
finally
{
conn.Close();
}
return(count);
}
public int InsertInternal(DataTable table, bool isHandle, out string errMsg)
{
int count = 0;
string tableName = "";
errMsg = "";
try
{
using (SqlBulkCopy bult =
new SqlBulkCopy(ConnectionStringFactory.CreateConnectionString(DatabaseEnum.Log4Net_CMD))
{
BatchSize = 800
})
{
tableName = "dbo.tbl_Interface_ProcessLog";
if (isHandle)
{
tableName = "dbo.tbl_Interface_HandleLog";
}
tableName = string.Format("{0}{1}", tableName, GetLogTableSuffix());
bult.ColumnMappings.Add("IKey", "ikey");
bult.ColumnMappings.Add("Username", "username");
bult.ColumnMappings.Add("LogTime", "logtime");
bult.ColumnMappings.Add("ClientIP", "clientip");
bult.ColumnMappings.Add("Module", "module");
bult.ColumnMappings.Add("OrderNo", "orderno");
bult.ColumnMappings.Add("LogType", "logtype");
bult.ColumnMappings.Add("Content", "content");
bult.ColumnMappings.Add("ServerIP", "ServerIP");
bult.ColumnMappings.Add("Keyword", "KeyWord");
bult.DestinationTableName = tableName;
bult.WriteToServer(table);
count = table.Rows.Count;
Logger.Debug(string.Format("批量成功往{0}-{1}表插入{2}条数据", tableName, table.TableName, count));
}
}
catch (Exception ex)
{
#if APPSERVER
//插入失败后写入文件，暂存，文件名为 表的名字.json
File.AppendAllText(GetBackFileName(table.TableName, isHandle), Newtonsoft.Json.JsonConvert.SerializeObject(table));
#endif
errMsg = GetString(ex);
count = 0;
}
#if APPSERVER
//ThreadPool.QueueUserWorkItem((state) =>
//{
// string msg = "";
// object[] arr = state as object[];
// LogMessageDAL d = arr[0] as LogMessageDAL;
// int cnt = d.InsertPostgres((DataTable)arr[1], (bool)arr[2], out msg);
// if (cnt == 0 && !string.IsNullOrEmpty(msg))
// {
// Logger.Error(string.Format("Postgresql批量失败，失败原因：{0}", msg));
// }
// else
// {
// Logger.Debug(string.Format("Postgresql批量成功，成功条数：{0}", cnt));
// }
//}, new object[] { this, table, isHandle });
#endif
return(count);
}