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