public void WriteSql(DataTable table, string tableName, SqlConnectionMode connectionMode) { Open(connectionMode); if (Connection == null) { return; } if (Connection.State != ConnectionState.Open) { return; } var rowArray = table.Select(); using (var bulkCopy = new SqlBulkCopy(Connection)) { bulkCopy.DestinationTableName = tableName; foreach (var c in table.Columns.OfType <DataColumn>()) { bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)); } bulkCopy.WriteToServer(rowArray); } Close(); }
private void Open(SqlConnectionMode connectionMode) { try { ConnectionMode = connectionMode; //var connect= new StackExchange.Profiling.Data.ProfiledDbConnection(new SqlConnection(ConnectionString), MiniProfiler.Current); Connection = new SqlConnection(ConnectionString); // Connection = connect.InnerConnection as SqlConnection; Connection.Open(); } catch (DataException ex) { Close(); } }
public int ExecuteNonQuery(string query, SqlConnectionMode connectionMode) { switch (connectionMode) { case SqlConnectionMode.DEFAULT: Open(SqlConnectionMode.DEFAULT); break; default: Open(SqlConnectionMode.DEFAULT); break; } Open(connectionMode); if (Connection == null) { return(-1); } if (Connection.State != ConnectionState.Open) { return(-1); } Command = new SqlCommand(query, Connection) { CommandType = CommandType.Text }; int returnValue = 0; try { returnValue = Command.ExecuteNonQuery(); } catch (Exception ex) { //Helpers.LogHelper.Insert($"SQL:{query}", ex); //LogHelper.Error(query + " exception " + ex); } Command.Dispose(); Close(); return(returnValue); }
public DataTable ExecuteQuery(string query, SqlConnectionMode model) { //switch (ConnectionMode) //{ // case SqlConnectionMode.BIGPARA: // Open(SqlConnectionMode.BIGPARA); // break; // case SqlConnectionMode.DBMATRIKS: // Open(SqlConnectionMode.DBMATRIKS); // break; // default: // Open(SqlConnectionMode.BIGPARA); // break; //} var sqlDataAdapter = new SqlDataAdapter(query, ConnectionString); var dt = new DataTable(); sqlDataAdapter.Fill(dt); sqlDataAdapter.Dispose(); Close(); return(dt); }
public void ExecuteBulkInsert(DataTable _table, SqlConnectionMode connectionMode) { Open(connectionMode); try { using (SqlBulkCopy s = new SqlBulkCopy(Connection)) { s.DestinationTableName = _table.TableName; foreach (var column in _table.Columns) { s.ColumnMappings.Add(column.ToString(), column.ToString()); } s.WriteToServer(_table); } } catch (Exception ex) { //LogHelper.Error(ex); } Close(); }
public DataSet ExecuteProcedureDataSets(string procedureName, IEnumerable <DbQueryParameter> parameters, SqlConnectionMode connectionMode) { Open(connectionMode); procedureName = procedureName.Trim(); if (Connection == null) { return(null); } if (Connection.State != ConnectionState.Open) { return(null); } Command = new SqlCommand(procedureName, Connection) { CommandType = CommandType.StoredProcedure }; if (parameters != null) { Command.Parameters.Clear(); foreach (var dbParameter in parameters) { var parameter = new SqlParameter { ParameterName = "@" + dbParameter.Name, Direction = dbParameter.Direction }; parameter.Value = dbParameter.Value; Command.Parameters.Add(parameter); } } var da = new SqlDataAdapter(); var dataSet = new DataSet(); try { da.SelectCommand = Command; da.Fill(dataSet); } catch (Exception x) { throw new Exception(); } finally { Command.Dispose(); da.Dispose(); Close(); } return(dataSet); }
public List <T> ExecuteProcedureWithDataAdapter <T>(string procedureName, IEnumerable <DbQueryParameter> parameters, SqlConnectionMode connectionMode) where T : class { List <T> returnObject = null; Open(connectionMode); if (Connection == null) { return(null); } if (Connection.State != ConnectionState.Open) { return(null); } Command = new SqlCommand(procedureName, Connection) { CommandType = CommandType.StoredProcedure }; // pass stored procedure parameters to command if (parameters != null) { Command.Parameters.Clear(); foreach (var dbParameter in parameters) { var parameter = new SqlParameter { ParameterName = "@" + dbParameter.Name, Direction = dbParameter.Direction }; //if (dbParameter.Value is DateTime) // parameter.Value = Convert.ToDateTime(dbParameter.Value).ToOADate(); //else parameter.Value = dbParameter.Value; Command.Parameters.Add(parameter); } } var da = new SqlDataAdapter(); var dt = new DataTable(); try { da.SelectCommand = Command; da.Fill(dt); returnObject = ConvertToList <T>(dt); } catch (Exception x) { //Helpers.LogHelper.Insert($"SQL:{procedureName}", x); //LogHelper.Error(procedureName + " exception " + x); } finally { Command.Dispose(); da.Dispose(); } return(returnObject); }
public int ExecuteScalar(string procedureName, List <DbQueryParameter> parameters, SqlConnectionMode connectionMode) { Open(connectionMode); int returnValue = 0; try { returnValue = (int)ExecuteProcedure(procedureName, ExecuteType.ExecuteScalar, parameters); } catch (Exception ex) { //LogHelper.Error(procedureName + " parameters:" + GetErrorMessage(parameters) + " exception " + ex); } UpdateOutParameters(); Close(); return(returnValue); }
// executes list query stored procedure and maps result generic list of objects public List <T> ExecuteList <T>(string procedureName, List <DbQueryParameter> parameters, SqlConnectionMode connectionMode) where T : new() { var listObjects = new List <T>(); Open(connectionMode); try { var reader = (IDataReader)ExecuteProcedure(procedureName, ExecuteType.ExecuteReader, parameters); while (reader.Read()) { var listItemObject = new T(); for (var i = 0; i < reader.FieldCount; i++) { try { SetObjectValue(reader.GetName(i), reader.GetValue(i), ref listItemObject); } catch (Exception ex) { var name = reader.GetName(i); //Helpers.LogHelper.Insert($"SQL:{procedureName} parameter {name}", ex); //LogHelper.Error(procedureName + " parameters:" + GetErrorMessage(parameters) + " result :" + reader.GetName(i) + " exception " + ex); } } listObjects.Add(listItemObject); } reader.Close(); UpdateOutParameters(); } catch (Exception ex) { //Helpers.LogHelper.Insert($"SQL:{procedureName}", ex); //LogHelper.Error(procedureName + " exception " + ex); } Close(); return(listObjects); }
// executes list query stored procedure without parameters public List <T> ExecuteList <T>(string procedureName, SqlConnectionMode connectionMode) where T : new() { return(ExecuteList <T>(procedureName, null, connectionMode)); }