private static object PrivateExecuteScalar(string CommandText, CommandType CommandType, SqlParameterCollection Parameters) { try { SqlConnection connection = DataAccessSql.GetConnection(); connection.Open(); SqlCommand command = DataAccessSql.GetCommand(); command.CommandText = CommandText; command.CommandType = CommandType; command.Connection = connection; if (Parameters != null) { foreach (SqlParameter parameter in (DbParameterCollection)Parameters) { command.Parameters.Add(new SqlParameter(parameter.ParameterName, parameter.SqlDbType, parameter.Size, parameter.Direction, parameter.Precision, parameter.Scale, parameter.SourceColumn, parameter.SourceVersion, parameter.SourceColumnNullMapping, parameter.Value, parameter.XmlSchemaCollectionDatabase, parameter.XmlSchemaCollectionOwningSchema, parameter.XmlSchemaCollectionName)); } } object obj = command.ExecuteScalar(); connection.Close(); return(obj); } catch (Exception ex) { throw ex; } }
public static string[] GetTableNamesFromDatabase() { SqlConnection connection = DataAccessSql.GetConnection(); string selectCommandText = "SELECT sys.objects.name AS TableNameOnly, sys.schemas.name AS SchemaName, sys.schemas.name + '.' + sys.objects.name AS TableName FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id WHERE sys.objects.type = 'U'"; DataSet dataSet1 = new DataSet(); try { new SqlDataAdapter(selectCommandText, connection).Fill(dataSet1); } catch (Exception ex) { SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("SELECT [name] AS TableName FROM sysobjects WHERE xtype='U' AND [name] <> 'dtproperties' ORDER BY [name]", connection); dataSet1 = new DataSet(); DataSet dataSet2 = dataSet1; sqlDataAdapter.Fill(dataSet2); } if (dataSet1 == null || dataSet1.Tables.Count == 0) { return((string[])null); } int count = dataSet1.Tables[0].Rows.Count; string[] strArray = new string[count]; for (int index = 0; index < count; ++index) { strArray[index] = dataSet1.Tables[0].Rows[index]["TableName"].ToString(); } return(strArray); }
private static DataSet RetrieveDataSet(string CommandText, CommandType CommandType, SqlParameterCollection Parameters) { try { DataSet dataSet = new DataSet(); SqlConnection connection = DataAccessSql.GetConnection(); connection.Open(); SqlDataAdapter dataAdapter = DataAccessSql.GetDataAdapter(CommandText, connection); SqlCommand command = DataAccessSql.GetCommand(); command.CommandText = CommandText; command.CommandType = CommandType; command.Connection = connection; dataAdapter.SelectCommand = command; if (Parameters != null) { foreach (SqlParameter parameter in (DbParameterCollection)Parameters) { dataAdapter.SelectCommand.Parameters.Add(new SqlParameter(parameter.ParameterName, parameter.SqlDbType, parameter.Size, parameter.Direction, parameter.Precision, parameter.Scale, parameter.SourceColumn, parameter.SourceVersion, parameter.SourceColumnNullMapping, parameter.Value, parameter.XmlSchemaCollectionDatabase, parameter.XmlSchemaCollectionOwningSchema, parameter.XmlSchemaCollectionName)); } } dataAdapter.Fill(dataSet); connection.Close(); return(dataSet); } catch (Exception ex) { throw ex; } }
public static DataSet GetTableSchema(string TableName) { try { using (SqlConnection connection = DataAccessSql.GetConnection()) { DataSet dataSet = new DataSet(); string selectCommandText; if (TableName.Contains(".")) { string str = TableName.Split('.')[0]; TableName = TableName.Replace(str + ".", ""); selectCommandText = "SELECT TOP 1 * FROM " + str + ".[" + TableName + "]"; } else { selectCommandText = "SELECT TOP 1 * FROM [" + TableName + "]"; } new SqlDataAdapter(selectCommandText, connection).FillSchema(dataSet, SchemaType.Source, TableName); return(dataSet); } } catch { return((DataSet)null); } }
private void CreateOutput(string ProcedureName, bool WriteFiles, bool WriteSP, string CommandText, string outputType = "", string outputFile = "") { if (WriteSP) { if (this.OverwriteExistingSps) { this.DropProcedure(ProcedureName); } int num = 1; DataAccessSql.ExecuteNonQuery(CommandText, (CommandType)num); } if (WriteFiles) { if (outputFile != "") { SaveToFile(CommandText, ProcedureName, outputType, outputFile); } else if (outputFile != "controller") { SaveToFile(CommandText, ProcedureName, outputType, outputFile); } else { SaveToFile(CommandText, ProcedureName, "sql"); } } }
public static object ExecuteScalar(string StoredProcName) { try { return(DataAccessSql.PrivateExecuteScalar(StoredProcName, CommandType.StoredProcedure, (SqlParameterCollection)null)); } catch (Exception ex) { throw ex; } }
public static object ExecuteScalar(string CommandText, CommandType CommandType) { try { return(DataAccessSql.PrivateExecuteScalar(CommandText, CommandType, (SqlParameterCollection)null)); } catch (Exception ex) { throw ex; } }
public static int ExecuteNonQuery(string StoredProcName, SqlParameterCollection Parameters) { try { return(DataAccessSql.PrivateExecuteNonQuery(StoredProcName, CommandType.StoredProcedure, Parameters)); } catch (Exception ex) { throw ex; } }
public static SqlDataReader GetDataReader(string StoredProcName) { try { return(DataAccessSql.RetrieveDataReader(StoredProcName, CommandType.StoredProcedure, (SqlParameterCollection)null)); } catch (Exception ex) { throw ex; } }
public static SqlDataReader GetDataReader(string CommandText, CommandType CommandType) { try { return(DataAccessSql.RetrieveDataReader(CommandText, CommandType, (SqlParameterCollection)null)); } catch (Exception ex) { throw ex; } }
public static DataSet GetDataSet(string StoredProcName, SqlParameterCollection Parameters) { try { return(DataAccessSql.RetrieveDataSet(StoredProcName, CommandType.StoredProcedure, Parameters)); } catch (Exception ex) { throw ex; } }
private void DropProcedure(string Name) { try { DataAccessSql.ExecuteNonQuery("DROP PROCEDURE " + Name, CommandType.Text); } catch (Exception ex) { throw ex; } }
public static SqlParameterCollection GetParametersCollection() { return(DataAccessSql.GetCommand().Parameters); }
public void GenerateStoreProcedures(string ConnectionString, DatabaseTable Table, bool CreateInsert, bool CreateSelect, bool CreateUpdate, bool CreateDelete, bool CreateSelectDetails, bool CreateTable, bool WriteFiles, bool WriteSP, bool CreateController) { var cleanTableName = string.Empty; var commandPrefix = string.Empty; var commandList = new List <CommandObj>(); string tempName = String.Empty; try { DataAccessSql.ConnectionString = ConnectionString; DataSet tableSchema = DataAccessSql.GetTableSchema(Table.TableName); cleanTableName = tableSchema.Tables[0].TableName.Replace("_", "").Replace("-", ""); commandPrefix = Table.SchemaName + "." + cleanTableName; if (CreateSelect) { tempName = cleanTableName + "SelectAll"; this.GenerateSelectProcedure(tempName, tableSchema, WriteFiles, WriteSP, Table.SchemaName); commandList.Add(new CommandObj() { Title = "GetAllCommand", Value = tempName }); } if (CreateSelectDetails) { tempName = cleanTableName + "Select"; this.GenerateSelectOneProcedure(tempName, tableSchema, WriteFiles, WriteSP, Table.SchemaName); commandList.Add(new CommandObj() { Title = "FillCommand", Value = tempName }); // Index Creation script tempName = cleanTableName + "SelectAll"; this.GenerateSelectViews(tempName, tableSchema, WriteFiles, WriteSP, Table.SchemaName); // Collection model script this.GenerateCollectionModel(cleanTableName); } if (CreateDelete) { tempName = cleanTableName + "Delete"; this.GenerateDeleteProcedure(tempName, tableSchema, WriteFiles, WriteSP, Table.SchemaName); commandList.Add(new CommandObj() { Title = "DeleteCommand", Value = tempName }); } if (CreateUpdate) { tempName = cleanTableName + "Update"; this.GenerateUpdateProcedure(tempName, tableSchema, WriteFiles, WriteSP, Table.SchemaName); commandList.Add(new CommandObj() { Title = "UpdateCommand", Value = tempName }); this.GenerateUpdateViews(tempName, tableSchema, true, false, Table.SchemaName); } if (CreateTable) { var createTable = new SqlTableCreator(); var conx = new SqlConnection(); conx.ConnectionString = ConnectionString; createTable.Connection = conx; var tabl = createTable.CreateFromDataTable(tableSchema.Tables[0]); SaveToFile(tabl, tableSchema.Tables[0].TableName, "table", tabl); } if (!CreateInsert) { return; } tempName = cleanTableName + "Insert"; this.GenerateInsertProcedure(tempName, tableSchema, WriteFiles, WriteSP, Table.SchemaName); commandList.Add(new CommandObj() { Title = "InsertCommand", Value = tempName }); // Index Creation script this.GenerateInsertViews(tempName, tableSchema, WriteFiles, WriteSP, Table.SchemaName); // Generate App User GenerateAppUser(); // Generate DB Context GenerateDBContext(); // Model Creation script GenerateModel(cleanTableName, commandList, tableSchema, "int", Table.SchemaName); GenerateController(cleanTableName, true); } catch (Exception ex) { throw ex; } }