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); }