private List <SchemaRow> BuildColumns(string RoutineName, string RoutineType, bool IsProcedure, DataTable dtSchema)
{
List <SchemaRow> Schema = new List <SchemaRow>();
for (int i = 0; i <= dtSchema.Columns.Count - 1; i++)
{
SchemaRow sr = new SchemaRow();
sr.Name = RoutineName;
sr.Type = RoutineType;
sr.Column_Name = dtSchema.Columns[i].ColumnName;
sr.Data_Type = this.TypeToSqlDbType(dtSchema.Columns[i].DataType).ToString();
sr.Ordinal_Position = dtSchema.Columns[i].Ordinal;
sr.Length = dtSchema.Columns[i].MaxLength;
sr.Precision = -1;
sr.Scale = -1;
sr.Nullable = dtSchema.Columns[i].AllowDBNull;
sr.IsIdentity = dtSchema.Columns[i].AutoIncrement;
sr.IsTable = false;
sr.IsView = false;
sr.IsPrimaryKey = dtSchema.Columns[i].AutoIncrement;
sr.IsForeignKey = false;
sr.IsProcedure = IsProcedure;
sr.IsFunction = !IsProcedure;
Schema.Add(sr);
}
return(Schema);
}
public static SchemaRow CreateSchemaRow(BacksightDataSet ds)
{
SchemaRow result = ds.Schema.NewSchemaRow();
result.SetDefaultValues();
return(result);
}
public SchemaRow AddSchemaRow(string UniqueName)
{
SchemaRow rowSchemaRow = ((SchemaRow)(this.NewRow()));
rowSchemaRow.ItemArray = new object[] {
UniqueName
};
this.Rows.Add(rowSchemaRow);
return(rowSchemaRow);
}
private List <SchemaRow> GetInitialTables(DataTable dtTableSchema)
{
List <SchemaRow> TableSchema = new List <SchemaRow>();
foreach (DataRow dr in dtTableSchema.Rows)
{
if (dr["TABLE_TYPE"].ToString().Equals("BASE TABLE", StringComparison.OrdinalIgnoreCase) | dr["TABLE_TYPE"].ToString().Equals("VIEW", StringComparison.OrdinalIgnoreCase))
{
SchemaRow tsr = new SchemaRow();
tsr.Name = dr["TABLE_NAME"].ToString();
tsr.Type = dr["TABLE_TYPE"].ToString();
TableSchema.Add(tsr);
}
}
return(TableSchema);
}
private SchemaRow SetParameterAttributes(string Routine_Name, string Routine_Type, DataRow dr)
{
SchemaRow sr = new SchemaRow();
sr.Name = Routine_Name;
sr.Type = Routine_Type;
sr.Parameter_Name = (object.ReferenceEquals(dr["PARAMETER_NAME"], DBNull.Value) ? "" : dr["PARAMETER_NAME"]).ToString();
sr.Parameter_Mode = (object.ReferenceEquals(dr["PARAMETER_MODE"], DBNull.Value) ? "" : dr["PARAMETER_MODE"]).ToString();
sr.IsInMode = sr.Parameter_Mode.Equals("IN");
sr.IsOutMode = sr.Parameter_Mode.Equals("OUT");
sr.IsInOutMode = sr.Parameter_Mode.Equals("INOUT");
sr.Data_Type = (object.ReferenceEquals(dr["DATA_TYPE"], DBNull.Value) ? "" : dr["DATA_TYPE"]).ToString();
sr.Ordinal_Position = Convert.ToInt64((object.ReferenceEquals(dr["ORDINAL_POSITION"], DBNull.Value) ? 0 : dr["ORDINAL_POSITION"]));
sr.Length = Convert.ToInt64((object.ReferenceEquals(dr["CHARACTER_MAXIMUM_LENGTH"], DBNull.Value) ? -1 : dr["CHARACTER_MAXIMUM_LENGTH"]));
sr.Precision = Convert.ToInt64((object.ReferenceEquals(dr["NUMERIC_PRECISION"], DBNull.Value) ? -1 : dr["NUMERIC_PRECISION"]));
sr.Scale = Convert.ToInt64((object.ReferenceEquals(dr["NUMERIC_SCALE"], DBNull.Value) ? -1 : dr["NUMERIC_SCALE"]));
sr.IsProcedure = sr.Type.Equals("PROCEDURE", StringComparison.OrdinalIgnoreCase);
sr.IsFunction = sr.Type.Equals("FUNCTION", StringComparison.OrdinalIgnoreCase);
return(sr);
}
private SchemaRow SetColumnAttributes(string Table_Name, string Table_Type, DataRow dr)
{
SchemaRow sr = new SchemaRow();
sr.Name = Table_Name;
sr.Type = Table_Type;
sr.Column_Name = dr["COLUMN_NAME"].ToString();
sr.Data_Type = dr["DATA_TYPE"].ToString();
sr.Ordinal_Position = Convert.ToInt64(dr["ORDINAL_POSITION"]);
sr.Length = Convert.ToInt64((object.ReferenceEquals(dr["CHARACTER_MAXIMUM_LENGTH"], DBNull.Value) ? -1 : dr["CHARACTER_MAXIMUM_LENGTH"]));
sr.Precision = Convert.ToInt64((object.ReferenceEquals(dr["NUMERIC_PRECISION"], DBNull.Value) ? -1 : dr["NUMERIC_PRECISION"]));
sr.Scale = Convert.ToInt64((object.ReferenceEquals(dr["NUMERIC_SCALE"], DBNull.Value) ? -1 : dr["NUMERIC_SCALE"]));
sr.Nullable = Convert.ToBoolean((dr["IS_NULLABLE"].ToString().Equals("YES", StringComparison.OrdinalIgnoreCase) ? true : false));
sr.IsIdentity = Convert.ToBoolean((dr["IS_IDENTITY"].ToString().Equals("YES") ? true : false));
sr.IsTable = sr.Type.Equals("BASE TABLE", StringComparison.OrdinalIgnoreCase);
sr.IsView = sr.Type.Equals("VIEW", StringComparison.OrdinalIgnoreCase);
sr.IsPrimaryKey = dr["CONSTRAINT_TYPE"].ToString().Equals("PRI", StringComparison.OrdinalIgnoreCase);
sr.IsForeignKey = dr["CONSTRAINT_TYPE"].ToString().Equals("FOREIGN KEY", StringComparison.OrdinalIgnoreCase);
return(sr);
}
public List <SchemaRow> GetSchema()
{
if (this.ColumnSchemaQuery.IndexOf(this.TableNamePlaceHolder) == -1)
{
throw new Exception("Required placeholder for table name: '" + this.TableNamePlaceHolder + "'. Eg: " + this.GetQuery(QueryEnum.ColumnQuery));
}
List <SchemaRow> Schema = new List <SchemaRow>();
//Using System.Data.SQLite.dll
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(this.ConnStr)) {
conn.Open();
DataTable dtTableSchema = new DataTable();
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.TableSchemaQuery;
using (System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd)) {
da.Fill(dtTableSchema);
}
}
List <SchemaRow> TableSchema = new List <SchemaRow>();
foreach (DataRow dr in dtTableSchema.Rows)
{
SchemaRow tsr = new SchemaRow();
tsr.Name = dr["name"].ToString();
tsr.Type = dr["type"].ToString();
TableSchema.Add(tsr);
}
foreach (SchemaRow tsr in TableSchema)
{
DataTable dtColumnSchema = new DataTable();
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.ColumnSchemaQuery.Replace(this.TableNamePlaceHolder, tsr.Name);
using (System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd)) {
da.Fill(dtColumnSchema);
}
}
List <SchemaRow> ColSchema = new List <SchemaRow>();
foreach (DataRow dr in dtColumnSchema.Rows)
{
SchemaRow sr = new SchemaRow();
sr.Name = tsr.Name;
sr.Type = tsr.Type;
sr.Column_Name = dr["name"].ToString();
sr.Data_Type = dr["type"].ToString();
sr.Ordinal_Position = Convert.ToInt64(dr["cid"]);
sr.Ordinal_Position += 1;
//Ordinal positions are zero based in SQLite, make it one based.
sr.Length = -1;
sr.Precision = -1;
sr.Scale = -1;
sr.Nullable = Convert.ToBoolean((dr["notnull"].ToString().Equals("1") ? true : false));
sr.IsIdentity = Convert.ToBoolean((dr["pk"].ToString().Equals("1") ? true : false));
sr.IsTable = sr.Type.Equals("TABLE", StringComparison.OrdinalIgnoreCase);
sr.IsView = sr.Type.Equals("VIEW", StringComparison.OrdinalIgnoreCase);
sr.IsPrimaryKey = sr.IsIdentity;
sr.IsForeignKey = false;
ColSchema.Add(sr);
}
//Sort by ordinal position (just in case)
ListSorter <SchemaRow> sorter = new ListSorter <SchemaRow>("Ordinal_Position asc");
ColSchema.Sort(sorter);
//Add to return
foreach (SchemaRow sr in ColSchema)
{
Schema.Add(sr);
}
}
conn.Close();
}
return(Schema);
}
public SchemaRowChangeEvent(SchemaRow row, System.Data.DataRowAction action)
{
this.eventRow = row;
this.eventAction = action;
}
public void RemoveSchemaRow(SchemaRow row)
{
this.Rows.Remove(row);
}
public void AddSchemaRow(SchemaRow row)
{
this.Rows.Add(row);
}