public static void Insert(CurrentSettings settings, Google.Apis.Drive.v3.Data.File file, string identifier)
{
try
{
string connStr = string.Empty;
if (settings.SQL_output_authenticationType == "Windows Authentication")
{
connStr = ConnectionString.SQL_Windows(settings.SQL_output_server, settings.SQL_output_database);
}
else
{
connStr = ConnectionString.SQL_Server(settings.SQL_output_server, settings.SQL_output_username, settings.SQL_output_password, settings.SQL_output_database);
}
if (identifier == null)
{
identifier = string.Empty;
}
SqlParameterList parms = new SqlParameterList();
parms.Add(new SqlParameter("@fileId", file.Id));
parms.Add(new SqlParameter("@name", file.Name));
parms.Add(new SqlParameter("@size", file.Size));
parms.Add(new SqlParameter("@mimeType", file.MimeType));
parms.Add(new SqlParameter("@webViewLink", file.WebViewLink));
parms.Add(new SqlParameter("@identifier", identifier));
ExecCmdNonQuery(connStr, "INSERT INTO [dbo].[CGoogleDrive] ([fileId] ,[name] ,[size] ,[mimeType] ,[webViewLink] ,[identifier]) VALUES (@fileId ,@name ,@size ,@mimeType ,@webViewLink ,@identifier)", CommandType.Text, parms);
}
catch (Exception ex)
{
ex.Write();
}
}
public BaseResultInfo GetLastLogItem(string logType, string server)
{
BaseResultInfo result = new BaseResultInfo();
SqlParameterList commandParams = new SqlParameterList();
commandParams.Add("@LogType", SqlDbType.NVarChar, logType);
commandParams.Add("@LogServer", SqlDbType.NVarChar, server);
try
{
using (SqlConnection conn = new SqlConnection(base.ConnectionString))
{
using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, SQL_GET_LAST_LOG_ITEM, commandParams.ToArray()))
{
while (dr.Read())
{
LogItem a = Serializer.DeSerialize <LogItem>(dr);
result.ResultObject = a;
}
}
}
}
catch (Exception ex)
{
if (result.Error == null)
{
result.Error = new BaseErrorInfo();
}
result.Error.BuildException(ex);
}
return(result);
}
public BaseResultInfo InsertLogItem(LogItem item)
{
BaseResultInfo result = new BaseResultInfo();
SqlParameterList commandParams = SqlSerializer.Serialize(item);
try
{
using (SqlConnection conn = new SqlConnection(base.ConnectionString))
{
conn.Open();
SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, SQL_ADD_LOG_ITEM, commandParams.ToArray());
result.ReturnValue = commandParams.GetReturnValue();
if (result.ReturnValue < 0)
{
throw new Exception("SQL_ADD_LOG_ITEM");
}
}
}
catch (Exception ex)
{
if (result.Error == null)
{
result.Error = new BaseErrorInfo();
}
result.Error.BuildException(ex);
}
return(result);
}
public BaseResultInfo SearchItems(LogItemRequest request)
{
BaseResultInfo result = new BaseResultInfo();
SqlParameterList commandParams = new SqlParameterList();
string select = " select * from LogTBL where InsertDate > @StartDate and InsertDate < @EndDate";
commandParams.Add("@StartDate", SqlDbType.DateTime, request.StartDate);
commandParams.Add("@EndDate", SqlDbType.DateTime, request.EndDate);
if (request.logType.HasValue)
{
select += " and LogType = @LogType";
commandParams.Add("@LogType", SqlDbType.NVarChar, request.logType.ToString());
}
if (!string.IsNullOrEmpty(request.SiteId))
{
select += " and SiteId = @SiteId";
commandParams.Add("@SiteId", SqlDbType.NVarChar, request.SiteId);
}
if (request.AccountId.HasValue)
{
select += " and AccountId = @AccountId";
commandParams.Add("@AccountId", SqlDbType.Int, request.AccountId);
}
List <LogItem> items = new List <LogItem>();
try
{
using (SqlConnection conn = new SqlConnection(base.ConnectionString))
{
using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.Text, select, commandParams.ToArray()))
{
while (dr.Read())
{
LogItem a = Serializer.DeSerialize <LogItem>(dr);
items.Add(a);
}
result.ResultObject = items;
}
}
}
catch (Exception ex)
{
if (result.Error == null)
{
result.Error = new BaseErrorInfo();
}
result.Error.BuildException(ex);
}
return(result);
}
public BaseResultInfo InsertLogItem(List <LogItem> items)
{
SiteLogger.ILogger logger = SiteLogger.LoggingFactory.GetLogger;
BaseResultInfo result = new BaseResultInfo();
List <ResultLogItem> errorData = new List <ResultLogItem>();
try
{
using (SqlConnection conn = new SqlConnection(base.ConnectionString))
{
conn.Open();
SqlParameterList commandParams = new SqlParameterList();
int successCount = 0;
foreach (var item in items)
{
try
{
commandParams = SqlSerializer.Serialize(item);
int res = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, SQL_ADD_LOG_ITEM, commandParams.ToArray());
successCount++;
}
catch (Exception ex)
{
logger.Error(string.Format("INDEX = {0}, DATETIME = {1}, LOG_TYPE = {2}, MESSAGE = {3}", item.Index, item.InsertDate, item.LogType, ex.Message));
errorData.Add(new ResultLogItem()
{
Log = item, ErrorMessage = ex.Message
});
}
}
result.ReturnValue = successCount; commandParams.GetReturnValue();
result.ResultObject = errorData;
conn.Close();
if (result.ReturnValue < 0)
{
throw new Exception("SQL_ADD_LOG_ITEM");
}
}
}
catch (Exception ex)
{
if (result.Error == null)
{
result.Error = new BaseErrorInfo();
}
result.Error.BuildException(ex);
}
return(result);
}
/// <summary>
/// Executes stored procedure and returns a set of tables
/// </summary>
/// <param name="procedureName">Name of the procedure</param>
/// <param name="parameterList">Procedure parameters</param>
protected static DataSet ExecuteProcedureMultiTable(string procedureName, SqlParameterList parameterList)
{
DataSet result = new DataSet();
using (SqlConnection connection = Connection)
{
SqlCommand command = new SqlCommand(procedureName, connection);
command.CommandTimeout = 0;
command.CommandType = CommandType.StoredProcedure;
if (parameterList != null && parameterList.Count > 0)
{
foreach (SqlParameter parameter in parameterList)
{
command.Parameters.Add(parameter);
}
}
SqlDataAdapter adapter = new SqlDataAdapter(command);
connection.Open();
adapter.Fill(result);
connection.Close();
}
return result;
}
public BaseResultInfo GetLoginsLength(DateTime start, DateTime?end)
{
BaseResultInfo result = new BaseResultInfo();
SqlParameterList commandParams = new SqlParameterList();
commandParams.Add("@StartDate", SqlDbType.DateTime, start);
commandParams.Add("@EndDate", SqlDbType.DateTime, end);
List <LoginLengthInfo> items = new List <LoginLengthInfo>();
try
{
using (SqlConnection conn = new SqlConnection(base.ConnectionString))
{
using (SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, SQL_GET_LOGINS_LENGTH, commandParams.ToArray()))
{
while (dr.Read())
{
LoginLengthInfo a = Serializer.DeSerialize <LoginLengthInfo>(dr);
items.Add(a);
}
result.ResultObject = items;
}
}
}
catch (Exception ex)
{
if (result.Error == null)
{
result.Error = new BaseErrorInfo();
}
result.Error.BuildException(ex);
}
return(result);
}
/// <summary>
/// Executes string and returns a table
/// </summary>
/// <param name="query">query string</param>
/// <param name="parameters">query parameters</param>
protected static DataTable ExecuteStringTable(string query, params SqlParameter[] parameters)
{
SqlParameterList list = new SqlParameterList();
foreach (SqlParameter parameter in parameters)
{
list.Add(parameter);
}
return ExecuteStringTable(query, list);
}
/// <summary>
/// Executes string and returns a table
/// </summary>
/// <param name="query">query string</param>
/// <param name="parameterList">query parameters</param>
protected static DataTable ExecuteStringTable(string query, SqlParameterList parameterList)
{
DataTable result = new DataTable();
using (SqlConnection connection = Connection)
{
SqlCommand command = new SqlCommand(query, connection);
command.CommandTimeout = 0;
command.CommandType = CommandType.Text;
if (parameterList != null && parameterList.Count > 0)
{
foreach (SqlParameter parameter in parameterList)
{
command.Parameters.Add(parameter);
}
}
SqlDataAdapter adapter = new SqlDataAdapter(command);
connection.Open();
adapter.Fill(result);
connection.Close();
}
return result;
}
/// <summary>
/// Executes string and returns a scalar result
/// </summary>
/// <param name="query">query string</param>
/// <param name="parameters">query parameters</param>
protected static object ExecuteStringScalar(string query, params SqlParameter[] parameters)
{
SqlParameterList list = new SqlParameterList();
foreach (SqlParameter parameter in parameters)
{
list.Add(parameter);
}
return ExecuteStringScalar(query, list);
}
/// <summary>
/// Executes string and returns a scalar result
/// </summary>
/// <param name="query">query string</param>
/// <param name="parameterList">query parameters</param>
protected static object ExecuteStringScalar(string query, SqlParameterList parameterList)
{
object result;
using (SqlConnection connection = Connection)
{
SqlCommand command = new SqlCommand(query, connection);
command.CommandTimeout = 0;
command.CommandType = CommandType.Text;
if (parameterList != null && parameterList.Count > 0)
{
foreach (SqlParameter parameter in parameterList)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
result = command.ExecuteScalar();
connection.Close();
}
return result;
}
/// <summary>
/// Executes string and returns a data reader
/// </summary>
/// <param name="query">query string</param>
/// <param name="parameters">query parameters</param>
protected static SqlDataReader ExecuteStringReader(string query, params SqlParameter[] parameters)
{
SqlParameterList list = new SqlParameterList();
foreach (SqlParameter parameter in parameters)
{
list.Add(parameter);
}
return ExecuteStringReader(query, list);
}
/// <summary>
/// Executes non-query string
/// </summary>
/// <param name="query">query string</param>
/// <param name="parameterList">query parameters</param>
protected static void ExecuteStringNonQuery(string query, SqlParameterList parameterList)
{
using (SqlConnection connection = Connection)
{
SqlCommand command = new SqlCommand(query, connection);
command.CommandTimeout = 0;
command.CommandType = CommandType.Text;
if (parameterList != null && parameterList.Count > 0)
{
foreach (SqlParameter parameter in parameterList)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
/// <summary>
/// Executes stored procedure and returns a scalar result
/// </summary>
/// <param name="procedureName">Name of the procedure</param>
/// <param name="parameters">Procedure parameters</param>
protected static object ExecuteProcedureScalar(string procedureName, params SqlParameter[] parameters)
{
SqlParameterList list = new SqlParameterList();
foreach (SqlParameter parameter in parameters)
{
list.Add(parameter);
}
return ExecuteProcedureScalar(procedureName, list);
}
/// <summary>
/// Executes stored procedure and returns a data reader
/// </summary>
/// <param name="procedureName">Name of the procedure</param>
/// <param name="parameters">Procedure parameters</param>
protected static SqlDataReader ExecuteProcedureReader(string procedureName, params SqlParameter[] parameters)
{
SqlParameterList list = new SqlParameterList();
foreach (SqlParameter parameter in parameters)
{
list.Add(parameter);
}
return ExecuteProcedureReader(procedureName, list);
}
/// <summary>
/// Executes non-query stored procedure
/// </summary>
/// <param name="procedureName">Name of the procedure</param>
/// <param name="parameters">Procedure parameters</param>
protected static void ExecuteProcedureNonQuery(string procedureName, params SqlParameter[] parameters)
{
SqlParameterList list = new SqlParameterList();
foreach (SqlParameter parameter in parameters)
{
list.Add(parameter);
}
ExecuteProcedureNonQuery(procedureName, list);
}
/// <summary>
/// Executes stored procedure and returns a set of tables
/// </summary>
/// <param name="procedureName">Name of the procedure</param>
/// <param name="parameters">Procedure parameters</param>
protected static DataSet ExecuteProcedureMultiTable(string procedureName, params SqlParameter[] parameters)
{
SqlParameterList list = new SqlParameterList();
foreach (SqlParameter parameter in parameters)
{
list.Add(parameter);
}
return ExecuteProcedureMultiTable(procedureName, list);
}
/// <summary>
/// Executes non-query query string
/// </summary>
/// <param name="query">query string</param>
/// <param name="parameters">query parameters</param>
protected static void ExecuteStringNonQuery(string query, params SqlParameter[] parameters)
{
SqlParameterList list = new SqlParameterList();
foreach (SqlParameter parameter in parameters)
{
list.Add(parameter);
}
ExecuteStringNonQuery(query, list);
}
/// <summary>
/// Executes string and returns a data reader
/// </summary>
/// <param name="query">query string</param>
/// <param name="parameterList">query parameters</param>
protected static SqlDataReader ExecuteStringReader(string query, SqlParameterList parameterList)
{
SqlConnection connection = Connection;
SqlCommand command = new SqlCommand(query, connection);
command.CommandTimeout = 0;
command.CommandType = CommandType.Text;
if (parameterList != null && parameterList.Count > 0)
{
foreach (SqlParameter parameter in parameterList)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
