public void WriteClass()
{
string _classdeclaration =
@"using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace " + MiscClass.Namespace + @".Orm_Tool.Tables
{
public class " + this.TableName + @"
{
";
foreach (Fields type in Fields)
{
_classdeclaration +=
@" public " + type.FieldType.ToString().Replace("Arr", "[]") + " " + type.FieldName + @" { get; set; }
";
}
_classdeclaration +=
@"
public " + this.TableName + @"() {}
}
}
";
MiscClass.WriteClass(_classdeclaration, MiscClass.ClassType.Table, this.TableName);
}
public void WriteClass()
{
int iterator = 0;
string _classdeclaration =
@"using System;
using System.Collections.Generic;
using System.Web;
using System.Linq;
using System.Data;
using Microsoft.SqlServer.Server;
namespace " + MiscClass.Namespace + @".Orm_Tool.Tables
{
public class " + this.ListName + @":List<" + this.TableName + @">,IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(";
foreach (Tuple <string, string, string, string> field in this.Fields)
{
_classdeclaration +=
@"
new SqlMetaData(""" + field.Item1 + @""",SqlDbType." + field.Item4 + "" + ((field.Item4.Equals("Text") || field.Item3.Equals(""))?"":"," + field.Item3) + @")";
iterator++;
if (iterator != this.Fields.Count)
{
_classdeclaration += ",";
}
}
_classdeclaration +=
@"
);
foreach (" + this.TableName + @" data in this)
{";
iterator = 0;
foreach (Tuple <string, string, string, string> field in Fields)
{
_classdeclaration +=
@"
if(CheckNullOrEmpty(data." + field.Item1 + @"))
{
ret.SetDBNull(" + iterator.ToString() + @");
}
else
{
ret.Set" + field.Item2 + @"(" + iterator.ToString() + @",data." + field.Item1 + @");
}
";
iterator++;
}
_classdeclaration +=
@" yield return ret;
}
}
private static bool CheckNullOrEmpty<T>(T value)
{
if (typeof(T) == typeof(string))
return string.IsNullOrEmpty(value as string);
return value == null || value.Equals(default(T));
}
}
}";
MiscClass.WriteClass(_classdeclaration, MiscClass.ClassType.TableList, this.ListName);
}
public static List <Table> GetTablesInfo()
{
List <Table> returnTable = new List <Table>();
Table TempTable = new Table();
SqlConnection con = new SqlConnection(MiscClass.ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("select name from sys.tables", con);
SqlDataReader dtreader = cmd.ExecuteReader();
string tablename;
while (dtreader.Read())
{
tablename = dtreader.GetValue(0).ToString();
if (tablename.Substring(tablename.Length - 3, 3).Equals("_TB"))
{
SqlConnection con2 = new SqlConnection(MiscClass.ConnectionString);
con2.Open();
SqlCommand cmd2 = new SqlCommand(@"SELECT a.Column_Name,a.Data_Type,CASE WHEN b.COLUMN_NAME IS NULL THEN 0 ELSE 1 END as 'IsPrimaryKey' , a.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS as a LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as b
ON a.Column_Name = b.COLUMN_NAME
AND b.CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'PRIMARY KEY' and table_name=@tablename)
where a.table_name=@tablename", con2);
cmd2.Parameters.AddWithValue("@tablename", tablename);
SqlDataReader dtreader2 = cmd2.ExecuteReader();
TempTable = new Table();
TempTable.TableName = tablename.Substring(0, tablename.Length - 3);
while (dtreader2.Read())
{
TempTable.Fields.Add(new Fields()
{
FieldName = dtreader2.GetValue(0).ToString(), FieldType = MiscClass.stringtoenum(dtreader2.GetValue(1).ToString())
});
TempTable.FieldsForQuery.Add(new Tuple <string, string, int, string>(dtreader2.GetValue(0).ToString(), dtreader2.GetValue(1).ToString(), Convert.ToInt32(dtreader2.GetValue(2)), dtreader2.GetValue(3) == DBNull.Value ? "" : dtreader2.GetValue(3).ToString()));
}
returnTable.Add(TempTable);
dtreader2.Close();
con2.Close();
}
}
dtreader.Close();
con.Close();
return(returnTable);
}
public static TableList GetTableList(Table table)
{
SqlConnection con = new SqlConnection(MiscClass.ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SELECT Column_Name , Data_Type , CHARACTER_MAXIMUM_LENGTH as Lenght FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @tablename", con);
cmd.Parameters.AddWithValue("@tablename", table.TableName + "_TB");
SqlDataReader dtreader = cmd.ExecuteReader();
TableList TempTableList = new TableList();
TempTableList.ListName = "List" + table.TableName;
TempTableList.TableName = table.TableName;
while (dtreader.Read())
{
TempTableList.Fields.Add(new Tuple <string, string, string, string>(dtreader.GetValue(0).ToString(), MiscClass.sqltypetonettype(dtreader.GetValue(1).ToString()).Replace("[]", "s"), dtreader.GetValue(2) == DBNull.Value ? "" : dtreader.GetValue(2).ToString(), MiscClass.dbtypetoenumeration(dtreader.GetValue(1).ToString())));
}
dtreader.Close();
con.Close();
return(TempTableList);
}
public void WriteClass()
{
string _methoddeclaration =
@"
public static object " + this.StoredProcedureName + " (";
for (int i = 0; i < Fields.Count; i++)
{
_methoddeclaration += Fields[i].Item2.Contains("_Type") ? MiscClass.Namespace + ".Orm_Tool.Tables.List" + Fields[i].Item2.Replace("_Type", "") + " " + Fields[i].Item1.Substring(1) : Fields[i].Item2 + " " + Fields[i].Item1.Substring(1);
if (i != (Fields.Count - 1))
{
_methoddeclaration += ",";
}
}
_methoddeclaration +=
@")
{
try
{
SqlConnection Connection = new SqlConnection(" + MiscClass.Namespace + @".Orm_Tool.DBInfo._GetConnectionString());
Connection.Open();
SqlCommand Command = new SqlCommand(""" + this.StoredProcedureName + @"_SP"", Connection);
Command.CommandType = System.Data.CommandType.StoredProcedure;
";
for (int i = 0; i < Fields.Count; i++)
{
if (Fields[i].Item2.Contains("_Type"))
{
_methoddeclaration +=
@"
SqlParameter Parameter" + i.ToString() + @" = Command.Parameters.AddWithValue(""" + Fields[i].Item1 + @""", " + Fields[i].Item1.Substring(1) + @");
Parameter" + i.ToString() + @".SqlDbType = System.Data.SqlDbType.Structured;";
}
else
{
_methoddeclaration +=
@"
Command.Parameters.AddWithValue(""" + Fields[i].Item1 + @"""," + Fields[i].Item1 + @")";
}
}
_methoddeclaration +=
@"
Command.ExecuteNonQuery();
Connection.Close();
return ""Operation Successfull !"";
}
catch(Exception ex)
{
return ex.Message;
}
}
";
MiscClass.WriteClass(_methoddeclaration, MiscClass.ClassType.StoreProcedure);
}
public static List <StoredProcedure> GetStoredProceduresInfo()
{
List <StoredProcedure> returnSPs = new List <StoredProcedure>();
StoredProcedure TempSP = new StoredProcedure();
SqlConnection con = new SqlConnection(MiscClass.ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("select name from sys.procedures", con);
SqlDataReader dtreader = cmd.ExecuteReader();
string spname;
while (dtreader.Read())
{
spname = dtreader.GetValue(0).ToString();
if (spname.Substring(spname.Length - 3, 3).Equals("_SP"))
{
SqlConnection con2 = new SqlConnection(MiscClass.ConnectionString);
con2.Open();
SqlCommand cmd2 = new SqlCommand("select 'Parameter_name' = name, 'Type' = type_name(user_type_id) from sys.parameters where object_id = object_id(@spname)", con2);
cmd2.Parameters.AddWithValue("@spname", spname);
SqlDataReader dtreader2 = cmd2.ExecuteReader();
TempSP = new StoredProcedure();
TempSP.StoredProcedureName = spname.Substring(0, spname.Length - 3);
while (dtreader2.Read())
{
TempSP.Fields.Add(new Tuple <string, string>(dtreader2.GetValue(0).ToString(), MiscClass.sqltypetonettype(dtreader2.GetValue(1).ToString())));
}
returnSPs.Add(TempSP);
dtreader2.Close();
con2.Close();
}
}
dtreader.Close();
con.Close();
return(returnSPs);
}
public void WriteClass()
{
string _classdeclaration =
@"using System;
using System.Collections.Generic;
using System.IO;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace " + MiscClass.Namespace + @".Orm_Tool.Views
{
public class " + this.ViewName + @"
{
private static string _query = ""SELECT * FROM " + this.ViewName + @"_VW"";
private static List<Tuple<string,object>> paramaters = new List<Tuple<string,object>>();
";
foreach (Fields type in Fields)
{
_classdeclaration +=
@" public " + type.FieldType.ToString().Replace("Arr", "[]") + " " + type.FieldName + @" { get; set; }
";
}
_classdeclaration +=
@"
public " + this.ViewName + @"() {}
public static void As(string keyword)
{
_query += "" AS "" + keyword;
}
public static void Where()
{
_query += "" WHERE "";
}
public static void Expression(string expression,List<Tuple<string,object>> param)
{
_query += expression;
paramaters.AddRange(param);
}
public static void And()
{
_query += "" AND "";
}
public static void Or()
{
_query += "" OR "";
}
public static void Orderby(string[] columns)
{
_query += "" ORDER BY "" + string.Join("","", columns);
}
public static void Paging(int TotalrecordPerpage , int PageNumber)
{
_query += "" Offset "" + ((TotalrecordPerpage * PageNumber) - TotalrecordPerpage).ToString() + "" ROWS FETCH NEXT ""+TotalrecordPerpage+"" ROWS ONLY "";
}
public static IList<" + this.ViewName + @"> Execute()
{
SqlConnection Connection = new SqlConnection(" + MiscClass.Namespace + @".Orm_Tool.DBInfo._GetConnectionString());
List<" + this.ViewName + @"> return" + this.ViewName + @" = new List<" + this.ViewName + @">();
try
{
Connection.Open();
SqlCommand Command = new SqlCommand(_query, Connection);
foreach (Tuple<string, object> data in paramaters)
{
Command.Parameters.AddWithValue(data.Item1, data.Item2);
}
SqlDataReader Datareader = Command.ExecuteReader();
while (Datareader.Read())
{
return" + this.ViewName + @".Add(new " + this.ViewName + @"()
{";
int iterator = 0;
foreach (Fields type in Fields)
{
if (!type.FieldType.ToString().Equals("String"))
{
_classdeclaration +=
@"
" + type.FieldName + @" = Datareader.Get" + type.FieldType.ToString().Replace("Arr", "s") + "(" + iterator + ")";
}
else
{
_classdeclaration +=
@"
" + type.FieldName + " = Datareader.GetValue(" + iterator + @") == DBNull.Value?"""":Datareader.Get" + type.FieldType.ToString() + @"(" + iterator + @")";
}
iterator++;
if (iterator != Fields.Count)
{
_classdeclaration += @",
";
}
}
_classdeclaration +=
@"
});
}
Datareader.Close();
Connection.Close();
paramaters.Clear();
_query = ""SELECT * FROM " + this.ViewName + @"_VW"";
}
catch (Exception ex)
{
Connection.Close();
paramaters.Clear();
_query = ""SELECT * FROM " + this.ViewName + @"_VW"";
throw new Exception(ex.Message);
}
return return" + this.ViewName + @".AsReadOnly();
}
}
}
";
MiscClass.WriteClass(_classdeclaration, MiscClass.ClassType.View, this.ViewName);
}
public static List <View> GetViewsInfo()
{
List <View> returnView = new List <View>();
View TempView = new View();
SqlConnection con = new SqlConnection(MiscClass.ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("select name from sys.views", con);
SqlDataReader dtreader = cmd.ExecuteReader();
string viewname;
while (dtreader.Read())
{
viewname = dtreader.GetValue(0).ToString();
if (viewname.Substring(viewname.Length - 3, 3).Equals("_VW"))
{
SqlConnection con2 = new SqlConnection(MiscClass.ConnectionString);
con2.Open();
SqlCommand cmd2 = new SqlCommand("SELECT Column_Name , Data_Type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @viewname", con2);
cmd2.Parameters.AddWithValue("@viewname", viewname);
SqlDataReader dtreader2 = cmd2.ExecuteReader();
TempView = new View();
TempView.ViewName = viewname.Substring(0, viewname.Length - 3);
while (dtreader2.Read())
{
TempView.Fields.Add(new Fields()
{
FieldName = dtreader2.GetValue(0).ToString(), FieldType = MiscClass.stringtoenum(dtreader2.GetValue(1).ToString())
});
}
returnView.Add(TempView);
dtreader2.Close();
con2.Close();
}
}
dtreader.Close();
con.Close();
return(returnView);
}
