codegen :
codegenaspxpage:
<%@ Page Title="Code Generator" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeFile="Codegen.aspx.cs" Inherits="Codegen" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<div>
<br />
<table align="center">
<tr>
<td>
<asp:Label ID="lblNamespace" runat="server" Text="NameSpace"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtNameSpace" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblTableName" runat="server" Text="Table Names"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlTableName" runat="server" AutoPostBack="true">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblColumns" runat="server" Text="Columns"></asp:Label>
</td>
<td>
<asp:ListBox ID="lstColumns" runat="server"></asp:ListBox>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnGenerate" runat="server" Text="Generate class"
onclick="btnGenerate_Click" /></td><td>
<asp:Button ID="btnGenerateSP" runat="server" Text="Generate SP's" onclick="btnGenerateSP_Click"
/></td>
</tr>
</table>
</div>
</asp:Content>
codegenaspx.cspage:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using Microsoft.CSharp;
using System.CodeDom.Compiler;
using System.CodeDom;
using System.Reflection;
using System.Collections;
using System.Configuration;
public partial class Codegen : System.Web.UI.Page
{
SqlConnection con = new SqlConnection();
DataTable dt3;
protected void Page_Load(object sender, EventArgs e)
{
string Conn = string.Empty;
Conn = ConfigurationManager.ConnectionStrings["connstr"].ToString();
con.ConnectionString = Conn;
con.Open();
if (!IsPostBack)
fillTableNames();
}
private void fillTableNames()
{
try
{
SqlDataAdapter da = new SqlDataAdapter("SELECT name FROM sys.tables", con);
SqlCommandBuilder cmd = new SqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
ddlTableName.DataSource = dt;
ddlTableName.DataTextField = "name";
ddlTableName.DataValueField = "name";
ListItem litem = new ListItem("--Select table name--", "-1");
ddlTableName.DataBind();
ddlTableName.Items.Insert(0, litem);
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
try
{
if (txtNameSpace.Text.Trim() != "")
{
string commstring = "Select COLUMN_NAME as name,CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string ' WHEN DATA_TYPE LIKE '%INT%' THEN 'int ' WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime ' WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] ' WHEN DATA_TYPE = 'BIT' THEN 'bool ' WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string ' ELSE 'object ' END as Datatype FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + ddlTableName.SelectedValue.Trim() + "'";
SqlDataAdapter da = new SqlDataAdapter(commstring, con);
SqlCommandBuilder cmd = new SqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
lstColumns.DataSource = dt;
lstColumns.DataTextField = "name";
lstColumns.DataValueField = "name";
lstColumns.DataBind();
GenerateClass(dt);
Session["dt"] = dt;
}
else
Response.Write("<script>alert('Enter the NameSpace')</script>");
}
catch (Exception ex)
{
throw ex;
}
}
private void GenerateClass(DataTable dt)
{
try
{
Stream codeFile = File.Open("D:\\" + ddlTableName.SelectedValue.Trim() + ".cs", FileMode.Create);
StreamWriter sw = new StreamWriter(codeFile);
CSharpCodeProvider cscp = new CSharpCodeProvider();
ICodeGenerator codeGenerator = cscp.CreateGenerator(sw);
CodeGeneratorOptions cgo = new CodeGeneratorOptions();
StringBuilder sbUsing = new StringBuilder();
#region listOfNamespaces
sbUsing.AppendLine("using System;");
sbUsing.AppendLine("using System.Configuration;");
sbUsing.AppendLine("using System.Data;");
sbUsing.AppendLine("using System.Data.SqlClient;");
sbUsing.AppendLine("using System.Linq;");
sbUsing.AppendLine("using System.Web;");
sbUsing.AppendLine("using System.Web.Security;");
sbUsing.AppendLine("using System.Xml.Linq;");
#endregion
CodeSnippetCompileUnit cscu = new CodeSnippetCompileUnit(sbUsing.ToString());
codeGenerator.GenerateCodeFromCompileUnit(cscu, sw, cgo);
CodeNamespace cnsCodeDom = new CodeNamespace(txtNameSpace.Text.Trim());
CodeTypeDeclaration clsDecl = new CodeTypeDeclaration();
clsDecl.Name = ddlTableName.SelectedValue.Trim();
clsDecl.IsClass = true;
clsDecl.TypeAttributes = TypeAttributes.Public;
cnsCodeDom.Types.Add(clsDecl);
CodeConstructor clsConstructor = new CodeConstructor();
clsConstructor.Attributes = MemberAttributes.Public;
clsDecl.Members.Add(clsConstructor);
if (dt != null && dt.Rows.Count > 0)
{
CodeMemberField clsMember = new CodeMemberField();
for (int i = 0; i < dt.Rows.Count; i++)
{
clsMember = new CodeMemberField();
clsMember.Name = "_" + dt.Rows[i]["name"].ToString();
clsMember.Attributes = MemberAttributes.Private;
clsMember.Type = new CodeTypeReference(dt.Rows[i]["Datatype"].ToString());
clsDecl.Members.Add(clsMember);
}
clsMember = new CodeMemberField();
clsMember.Name = "con";
clsMember.Attributes = MemberAttributes.Public;
clsMember.Type = new CodeTypeReference("SqlConnection");
clsDecl.Members.Add(clsMember);
clsMember = new CodeMemberField();
clsMember.Name = "com";
clsMember.Attributes = MemberAttributes.Public;
clsMember.Type = new CodeTypeReference("SqlCommand");
clsDecl.Members.Add(clsMember);
clsMember = new CodeMemberField();
clsMember.Name = "da";
clsMember.Attributes = MemberAttributes.Public;
clsMember.Type = new CodeTypeReference("SqlDataAdapter");
clsDecl.Members.Add(clsMember);
CodeMemberProperty property = new CodeMemberProperty();
for (int j = 0; j < dt.Rows.Count; j++)
{
property = new CodeMemberProperty();
property.Name = dt.Rows[j]["name"].ToString();
property.Type = new CodeTypeReference(dt.Rows[j]["Datatype"].ToString());
property.Attributes = MemberAttributes.Public;
property.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + dt.Rows[j]["name"].ToString())));
property.SetStatements.Add(new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + dt.Rows[j]["name"].ToString()), new CodePropertySetValueReferenceExpression()));
clsDecl.Members.Add(property);
}
#region Delete
string comText = "select t.name as TABLE_NAME,c.name as COLUMN_NAME from sys.key_constraints as k join sys.tables as t on t.object_id = k.parent_object_id join sys.schemas as s on s.schema_id = t.schema_id join sys.index_columns as ic on ic.object_id = t.object_id and ic.index_id = k.unique_index_id join sys.columns as c on c.object_id = t.object_id and c.column_id = ic.column_id";
// This returns all the tablenames and their primarykey datafield names respetively
SqlDataAdapter da1 = new SqlDataAdapter(comText, con);
DataTable dt1 = new DataTable();
da1.Fill(dt1);
string primaryColName = string.Empty;
for (int i = 0; i < dt1.Rows.Count; i++)
{
string colName = dt1.Rows[i]["TABLE_NAME"].ToString();
if (colName == ddlTableName.SelectedItem.Value)
{
primaryColName = dt1.Rows[i]["COLUMN_NAME"].ToString();
break;
}
}
string tblName = ddlTableName.SelectedItem.Value;
string param = "obj" + tblName;
//ddlTableName.SelectedItem.Value = table name
// dt1 consists of 2 columns:all the tblNames in the selected database and Primary-key of that particular table
string comText1 = "SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tblName + "'";
SqlDataAdapter da2 = new SqlDataAdapter(comText1, con);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
dt3 = new DataTable();
dt3 = dt2.Copy();
// remove at position
string comt = @"select c.colid from sysindexes i join sysobjects o ON i.id = o.id join sysobjects pk ON i.name = pk.name AND pk.parent_obj = i.id
AND pk.xtype = 'PK' join sysindexkeys ik on i.id = ik.id and i.indid = ik.indid join syscolumns c ON ik.id = c.id AND ik.colid = c.colid
where o.name = '{0}' order by ik.keyno";
SqlCommand cmdIndex = new SqlCommand(string.Format(comt, tblName), con);
int colIndex = -1;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
colIndex = Convert.ToInt32(cmdIndex.ExecuteScalar());
}
catch (SqlException sqlEx)
{
throw sqlEx;
}
finally
{
con.Close();
}
//if (colIndex != -1)
//{
// colIndex = colIndex - 1;
// dt3.Rows.RemoveAt(colIndex);
//}
// This gives the no. of rows affected
int count = dt2.Rows.Count;
string[] colTypeList = new string[count];//
//This gives the list of Selected table column data types
string[] colNameList = new string[count];
// This gives the list of selected table column names
for (int i = 0; i < count; i++)
{
colTypeList[i] = dt2.Rows[i]["DATA_TYPE"].ToString();//
// dt2 table helps in finding the data type
colNameList[i] = dt2.Rows[i][0].ToString();
}
ArrayList pryRemove = new ArrayList();
//This gives the list of selected table column names list without primarykey column
for (int i = 0; i < count; i++)
{
if (colNameList[i] == primaryColName)
continue;
else
{
pryRemove.Add(colNameList[i]);
}
}
#endregion
#region Deletenew
CodeMemberMethod methodDele = new CodeMemberMethod();
methodDele.Attributes = MemberAttributes.Public;
methodDele.Name = "Delete";
methodDele.ReturnType = new CodeTypeReference("System.Int32");
methodDele.Parameters.Add(new CodeParameterDeclarationExpression(tblName, param));
StringBuilder sbDele = new StringBuilder();
sbDele.Append("int i=0;");
sbDele.Append("\n");
sbDele.Append("try {");
sbDele.Append("\n");
sbDele.Append("i= "+ ddlTableName.SelectedValue.Trim() + "DataAccess.Delete" + ddlTableName.SelectedValue + "(" + param + ");");
sbDele.Append("\n");
sbDele.Append(" }");
sbDele.Append("\n");
sbDele.Append("catch(Exception ex)");
sbDele.Append("\n");
sbDele.Append("{");
sbDele.Append("\n");
sbDele.Append("throw ex;");
sbDele.Append("\n");
sbDele.Append("}");
methodDele.Statements.Add(new CodeSnippetStatement(sbDele.ToString()));
methodDele.Statements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression("i")));
clsDecl.Members.Add(methodDele);
#endregion
#region Getall
CodeMemberMethod methodSelectAll = new CodeMemberMethod();
methodSelectAll.Attributes = MemberAttributes.Public;
methodSelectAll.Name = "GetAll";
methodSelectAll.ReturnType = new CodeTypeReference("DataTable");
StringBuilder sbSelectAll = new StringBuilder();
sbSelectAll.Append("DataTable dtGetall=new DataTable();");
sbSelectAll.Append("\n");
sbSelectAll.Append("try {");
sbSelectAll.Append("\n");
sbSelectAll.Append("dtGetall= " + ddlTableName.SelectedValue.Trim() + "DataAccess.GetAll" + ddlTableName.SelectedValue + "();");
sbSelectAll.Append("\n");
sbSelectAll.Append(" }");
sbSelectAll.Append("\n");
sbSelectAll.Append("catch(Exception ex)");
sbSelectAll.Append("\n");
sbSelectAll.Append("{");
sbSelectAll.Append("\n");
sbSelectAll.Append("throw ex;");
sbSelectAll.Append("\n");
sbSelectAll.Append("}");
methodSelectAll.Statements.Add(new CodeSnippetStatement(sbSelectAll.ToString()));
methodSelectAll.Statements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression("dtGetall")));
clsDecl.Members.Add(methodSelectAll);
#endregion
#region Updatenew
CodeMemberMethod methodUpdate = new CodeMemberMethod();
methodUpdate.Attributes = MemberAttributes.Public;
methodUpdate.Name = "Update";
methodUpdate.ReturnType = new CodeTypeReference("System.Int32");
methodUpdate.Parameters.Add(new CodeParameterDeclarationExpression(tblName, param));
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.Append("int i=0;");
sbUpdate.Append("\n");
sbUpdate.Append("try {");
sbUpdate.Append("\n");
sbUpdate.Append("i= " + ddlTableName.SelectedValue.Trim() + "DataAccess.Update" + ddlTableName.SelectedValue + "(" + param + ");");
sbUpdate.Append("\n");
sbUpdate.Append(" }");
sbUpdate.Append("\n");
sbUpdate.Append("catch(Exception ex)");
sbUpdate.Append("\n");
sbUpdate.Append("{");
sbUpdate.Append("\n");
sbUpdate.Append("throw ex;");
sbUpdate.Append("\n");
sbUpdate.Append("}");
methodUpdate.Statements.Add(new CodeSnippetStatement(sbUpdate.ToString()));
methodUpdate.Statements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression("i")));
clsDecl.Members.Add(methodUpdate);
#endregion
#region Insertnew
CodeMemberMethod methodInsert = new CodeMemberMethod();
methodInsert.Attributes = MemberAttributes.Public;
methodInsert.Name = "Insert";
methodInsert.ReturnType = new CodeTypeReference("System.Int32");
methodInsert.Parameters.Add(new CodeParameterDeclarationExpression(tblName, param));
StringBuilder sbInsert = new StringBuilder();
sbInsert.Append("int i=0;");
sbInsert.Append("\n");
sbInsert.Append("try {");
sbInsert.Append("\n");
sbInsert.Append("i="+ddlTableName.SelectedValue.Trim() + "DataAccess.Insert"+ ddlTableName.SelectedValue +"(" + param + ");");
sbInsert.Append("\n");
sbInsert.Append(" }");
sbInsert.Append("\n");
sbInsert.Append("catch(Exception ex)");
sbInsert.Append("\n");
sbInsert.Append("{");
sbInsert.Append("\n");
sbInsert.Append("throw ex;");
sbInsert.Append("\n");
sbInsert.Append("}");
methodInsert.Statements.Add(new CodeSnippetStatement(sbInsert.ToString()));
methodInsert.Statements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression("i")));
clsDecl.Members.Add(methodInsert);
#endregion
#region getByPrimarykey
//CodeMemberMethod methodByPrimarykey = new CodeMemberMethod();
//methodByPrimarykey.Attributes = MemberAttributes.Public;
//methodByPrimarykey.Name = "getByPrimarykey";
//methodByPrimarykey.ReturnType = new CodeTypeReference("System.Object");
//methodByPrimarykey.Parameters.Add(new CodeParameterDeclarationExpression("System.Int32", "id"));
//StringBuilder sbGetByPrimarykey = new StringBuilder();
//string tableName = ddlTableName.SelectedItem.Value;
//con.Open();
////con.ChangeDatabase("SwamiNarayansch");
//SqlDataAdapter dam = new SqlDataAdapter("SELECT * FROM " + tableName, con);
//DataTable dta = new DataTable();
//dam.Fill(dta);
//int m = dta.Columns.Count;
//sbGetByPrimarykey.Append(@"da = new SqlDataAdapter(""SELECT * FROM ");
//sbGetByPrimarykey.Append(ddlTableName.SelectedItem.Value);
//sbGetByPrimarykey.Append(" WHERE ");
//sbGetByPrimarykey.Append(primaryColName + " = ");
//sbGetByPrimarykey.AppendLine(@"""+id,con);");
////sbGetByPrimarykey.Append("i");
////sbGetByPrimarykey.AppendLine(@",con);");
//sbGetByPrimarykey.AppendLine("DataTable dt = new DataTable();");
//sbGetByPrimarykey.AppendLine("da.Fill(dt);");
//for (int i = 0; i < m; i++)
//{
// sbGetByPrimarykey.Append(param);
// sbGetByPrimarykey.Append(".");
// sbGetByPrimarykey.Append("_");
// sbGetByPrimarykey.Append(colNameList[i] + " = ");
// sbGetByPrimarykey.Append("dt.Rows[0]");
// sbGetByPrimarykey.Append(@"[""");
// sbGetByPrimarykey.Append(colNameList[i]);
// sbGetByPrimarykey.AppendLine(@"""].ToString();");
//}
//methodByPrimarykey.Statements.Add(new CodeSnippetStatement(sbGetByPrimarykey.ToString()));
//methodByPrimarykey.Statements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression(param)));
//clsDecl.Members.Add(methodByPrimarykey);
#endregion
GenerateDataAccess(dt);
}
codeGenerator.GenerateCodeFromNamespace(cnsCodeDom, sw, cgo);
sw.Close();
codeFile.Close();
}
catch (Exception ex)
{
throw ex;
}
}
private void GenerateDataAccess(DataTable dt)
{
try
{
Stream codeFile = File.Open("D:\\" + ddlTableName.SelectedValue.Trim() + "DataAccess.cs", FileMode.Create);
StreamWriter sw = new StreamWriter(codeFile);
CSharpCodeProvider cscp = new CSharpCodeProvider();
ICodeGenerator codeGenerator = cscp.CreateGenerator(sw);
CodeGeneratorOptions cgo = new CodeGeneratorOptions();
StringBuilder sbUsing = new StringBuilder();
#region listOfNamespaces
sbUsing.AppendLine("using System;");
sbUsing.AppendLine("using System.Configuration;");
sbUsing.AppendLine("using System.Data;");
sbUsing.AppendLine("using System.Data.SqlClient;");
sbUsing.AppendLine("using System.Linq;");
sbUsing.AppendLine("using System.Web;");
sbUsing.AppendLine("using System.Web.Security;");
sbUsing.AppendLine("using System.Xml.Linq;");
#endregion
CodeSnippetCompileUnit cscu = new CodeSnippetCompileUnit(sbUsing.ToString());
codeGenerator.GenerateCodeFromCompileUnit(cscu, sw, cgo);
CodeNamespace cnsCodeDom = new CodeNamespace(txtNameSpace.Text.Trim());
CodeTypeDeclaration clsDecl = new CodeTypeDeclaration();
clsDecl.Name = ddlTableName.SelectedValue.Trim() + "DataAccess";
clsDecl.IsClass = true;
clsDecl.TypeAttributes = TypeAttributes.Public;
cnsCodeDom.Types.Add(clsDecl);
CodeConstructor clsConstructor = new CodeConstructor();
clsConstructor.Attributes = MemberAttributes.Public;
clsDecl.Members.Add(clsConstructor);
if (dt != null && dt.Rows.Count > 0)
{
CodeMemberField clsMember = new CodeMemberField();
clsMember = new CodeMemberField();
clsMember.Name = "con";
clsMember.Attributes = MemberAttributes.Public;
clsMember.Type = new CodeTypeReference("SqlConnection");
clsDecl.Members.Add(clsMember);
clsMember = new CodeMemberField();
clsMember.Name = "cmd";
clsMember.Attributes = MemberAttributes.Public;
clsMember.Type = new CodeTypeReference("SqlCommand");
clsDecl.Members.Add(clsMember);
clsMember = new CodeMemberField();
clsMember.Name = "da";
clsMember.Attributes = MemberAttributes.Public;
clsMember.Type = new CodeTypeReference("SqlDataAdapter");
clsDecl.Members.Add(clsMember);
CodeMemberProperty property = new CodeMemberProperty();
string str = @"ConfigurationManager.ConnectionStrings[""connstr""]" + ".ConnectionString";
property = new CodeMemberProperty();
property.Name = "ConnectionString";
property.Type = new CodeTypeReference("String");
property.Attributes = MemberAttributes.Public | MemberAttributes.Static;
property.GetStatements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression(str)));
clsDecl.Members.Add(property);
#region Delete
string comText = "select t.name as TABLE_NAME,c.name as COLUMN_NAME from sys.key_constraints as k join sys.tables as t on t.object_id = k.parent_object_id join sys.schemas as s on s.schema_id = t.schema_id join sys.index_columns as ic on ic.object_id = t.object_id and ic.index_id = k.unique_index_id join sys.columns as c on c.object_id = t.object_id and c.column_id = ic.column_id";
// This returns all the tablenames and their primarykey datafield names respetively
SqlDataAdapter da1 = new SqlDataAdapter(comText, con);
DataTable dt1 = new DataTable();
da1.Fill(dt1);
string primaryColName = string.Empty;
for (int i = 0; i < dt1.Rows.Count; i++)
{
string colName = dt1.Rows[i]["TABLE_NAME"].ToString();
if (colName == ddlTableName.SelectedItem.Value)
{
primaryColName = dt1.Rows[i]["COLUMN_NAME"].ToString();
break;
}
}
string tblName = ddlTableName.SelectedItem.Value;
string param = "obj" + tblName;
//ddlTableName.SelectedItem.Value = table name
// dt1 consists of 2 columns:all the tblNames in the selected database and Primary-key of that particular table
string comText1 = "SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tblName + "'";
SqlDataAdapter da2 = new SqlDataAdapter(comText1, con);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
dt3 = new DataTable();
dt3 = dt2.Copy();
// remove at position
string comt = @"select c.colid from sysindexes i join sysobjects o ON i.id = o.id join sysobjects pk ON i.name = pk.name AND pk.parent_obj = i.id
AND pk.xtype = 'PK' join sysindexkeys ik on i.id = ik.id and i.indid = ik.indid join syscolumns c ON ik.id = c.id AND ik.colid = c.colid
where o.name = '{0}' order by ik.keyno";
SqlCommand cmdIndex = new SqlCommand(string.Format(comt, tblName), con);
int colIndex = -1;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
colIndex = Convert.ToInt32(cmdIndex.ExecuteScalar());
}
catch (SqlException sqlEx)
{
throw sqlEx;
}
finally
{
con.Close();
}
//if (colIndex != -1)
//{
// colIndex = colIndex - 1;
// dt3.Rows.RemoveAt(colIndex);
//}
// This gives the no. of rows affected
int count = dt2.Rows.Count;
string[] colTypeList = new string[count];//
//This gives the list of Selected table column data types
string[] colNameList = new string[count];
// This gives the list of selected table column names
for (int i = 0; i < count; i++)
{
colTypeList[i] = dt2.Rows[i]["DATA_TYPE"].ToString();//
// dt2 table helps in finding the data type
colNameList[i] = dt2.Rows[i][0].ToString();
}
ArrayList pryRemove = new ArrayList();
//This gives the list of selected table column names list without primarykey column
for (int i = 0; i < count; i++)
{
if (colNameList[i] == primaryColName)
continue;
else
{
pryRemove.Add(colNameList[i]);
}
}
ArrayList pryAdd = new ArrayList();
//This gives the list of selected table column names list without primarykey column
for (int i = 0; i < count; i++)
{
pryAdd.Add(colNameList[i]);
}
CodeMemberMethod methodDel = new CodeMemberMethod();
methodDel.Attributes = MemberAttributes.Public | MemberAttributes.Static;
methodDel.Name = "Delete" + ddlTableName.SelectedItem;
methodDel.ReturnType = new CodeTypeReference("System.Int32");
methodDel.Parameters.Add(new CodeParameterDeclarationExpression(tblName, param));
StringBuilder sbDel = new StringBuilder();
sbDel.Append(@"int i=0;");
sbDel.Append("\n");
sbDel.Append("try {");
sbDel.Append("\n");
sbDel.Append(@"SqlConnection con = new SqlConnection(ConnectionString);");
sbDel.Append("\n");
sbDel.Append(@"SqlCommand cmd = new SqlCommand(""sp_Delete" + ddlTableName.SelectedItem);
sbDel.Append(@""");");
sbDel.Append("\n");
sbDel.Append(@"cmd.CommandType = CommandType.StoredProcedure;");
sbDel.Append("\n");
sbDel.Append(@"cmd.Connection = con;");
sbDel.Append("\n");
sbDel.Append(@"con.Open();");
sbDel.Append("\n");
sbDel.Append(@"cmd.Parameters.AddWithValue(");//db.AddInParameter(dbCmd, "@AddressID", DbType.Int32, AddressID);
sbDel.Append(@"""");
sbDel.Append("@" + primaryColName.ToString());
sbDel.Append(@"""");
sbDel.Append(",");
sbDel.Append(param + "." + primaryColName);
sbDel.Append(");");
sbDel.Append("\n");
sbDel.Append("i = cmd.ExecuteNonQuery();");
sbDel.Append("\n");
sbDel.Append(" }");
sbDel.Append("\n");
sbDel.Append("catch(Exception ex)");
sbDel.Append("\n");
sbDel.Append("{");
sbDel.Append("\n");
sbDel.Append("throw ex;");
sbDel.Append("\n");
sbDel.Append("}");
methodDel.Statements.Add(new CodeSnippetStatement(sbDel.ToString()));
methodDel.Statements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression("i")));
clsDecl.Members.Add(methodDel);
#endregion
#region SelectAll
CodeMemberMethod methodSelectAll = new CodeMemberMethod();
methodSelectAll.Attributes = MemberAttributes.Public | MemberAttributes.Static; ;
methodSelectAll.Name = "GetAll" + ddlTableName.SelectedItem;
methodSelectAll.ReturnType = new CodeTypeReference("DataTable");
StringBuilder sbSelectAll = new StringBuilder();
sbSelectAll.Append(@"DataTable dt=new DataTable();");
sbSelectAll.Append("\n");
sbSelectAll.Append("try {");
sbSelectAll.Append("\n");
sbSelectAll.Append(@"SqlConnection con = new SqlConnection(ConnectionString);");
sbSelectAll.Append("\n");
sbSelectAll.Append(@"SqlCommand cmd = new SqlCommand(""sp_Get" + ddlTableName.SelectedItem);
sbSelectAll.Append(@""");");
sbSelectAll.Append("\n");
sbSelectAll.Append(@"cmd.CommandType = CommandType.StoredProcedure;");
sbSelectAll.Append("\n");
sbSelectAll.Append(@"cmd.Connection = con;");
sbSelectAll.Append("\n");
sbSelectAll.Append(@"con.Open();");
sbSelectAll.Append("\n");
sbSelectAll.Append(@"SqlDataReader rdr = cmd.ExecuteReader();");
sbSelectAll.Append("\n");
sbSelectAll.Append(@"if (rdr.HasRows)");
sbSelectAll.Append("\n");
sbSelectAll.Append(@"{");
sbSelectAll.Append("\n");
sbSelectAll.Append(@"dt.Load(rdr);");
sbSelectAll.Append("\n");
sbSelectAll.Append(@"}");
sbSelectAll.Append("\n");
sbSelectAll.Append(" }");
sbSelectAll.Append("\n");
sbSelectAll.Append("catch(Exception ex)");
sbSelectAll.Append("\n");
sbSelectAll.Append("{");
sbSelectAll.Append("\n");
sbSelectAll.Append("throw ex;");
sbSelectAll.Append("\n");
sbSelectAll.Append("}");
methodSelectAll.Statements.Add(new CodeSnippetStatement(sbSelectAll.ToString()));
methodSelectAll.Statements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression("dt")));
clsDecl.Members.Add(methodSelectAll);
#endregion
#region Update
CodeMemberMethod methodUpdate = new CodeMemberMethod();
methodUpdate.Attributes = MemberAttributes.Public | MemberAttributes.Static;
methodUpdate.Name = "Update" + ddlTableName.SelectedItem;
methodUpdate.ReturnType = new CodeTypeReference("System.Int32");
methodUpdate.Parameters.Add(new CodeParameterDeclarationExpression(tblName, param));
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.Append(@"int i=0;");
sbUpdate.Append("\n");
sbUpdate.Append("try {");
sbUpdate.Append("\n");
sbUpdate.Append(@"SqlConnection con = new SqlConnection(ConnectionString);");
sbUpdate.Append("\n");
sbUpdate.Append(@"SqlCommand cmd = new SqlCommand(""sp_Update" + ddlTableName.SelectedItem);
sbUpdate.Append(@""");");
sbUpdate.Append("\n");
sbUpdate.Append(@"cmd.CommandType = CommandType.StoredProcedure;");
sbUpdate.Append("\n");
sbUpdate.Append(@"cmd.Connection = con;");
sbUpdate.Append("\n");
sbUpdate.Append(@"con.Open();");
sbUpdate.Append("\n");
sbUpdate.Append(GenerateParametersforSp(pryAdd, pryAdd.Count, param, dt2));
sbUpdate.Append("i = cmd.ExecuteNonQuery();");
sbUpdate.Append("\n");
sbUpdate.Append(" }");
sbUpdate.Append("\n");
sbUpdate.Append("catch(Exception ex)");
sbUpdate.Append("\n");
sbUpdate.Append("{");
sbUpdate.Append("\n");
sbUpdate.Append("throw ex;");
sbUpdate.Append("\n");
sbUpdate.Append("}");
methodUpdate.Statements.Add(new CodeSnippetStatement(sbUpdate.ToString()));
methodUpdate.Statements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression("i")));
clsDecl.Members.Add(methodUpdate);
#endregion
#region Insert
CodeMemberMethod methodInsert = new CodeMemberMethod();
methodInsert.Attributes = MemberAttributes.Public | MemberAttributes.Static;
methodInsert.Name = "Insert" + ddlTableName.SelectedItem;
methodInsert.ReturnType = new CodeTypeReference("System.Int32");
methodInsert.Parameters.Add(new CodeParameterDeclarationExpression(tblName, param));
StringBuilder sbInsert = new StringBuilder();
sbInsert.Append(@"int i=0;");
sbInsert.Append("\n");
sbInsert.Append("try {");
sbInsert.Append("\n");
sbInsert.Append(@"SqlConnection con = new SqlConnection(ConnectionString);");
sbInsert.Append("\n");
sbInsert.Append(@"SqlCommand cmd = new SqlCommand(""sp_Insert" + ddlTableName.SelectedItem);
sbInsert.Append(@""");");
sbInsert.Append("\n");
sbInsert.Append(@"cmd.CommandType = CommandType.StoredProcedure;");
sbInsert.Append("\n");
sbInsert.Append(@"cmd.Connection = con;");
sbInsert.Append("\n");
sbInsert.Append(@"con.Open();");
sbInsert.Append("\n");
sbInsert.Append(GenerateParametersforSp(pryRemove, pryRemove.Count, param, dt3));
sbInsert.Append("i = cmd.ExecuteNonQuery();");
sbInsert.Append("\n");
sbInsert.Append(" }");
sbInsert.Append("\n");
sbInsert.Append("catch(Exception ex)");
sbInsert.Append("\n");
sbInsert.Append("{");
sbInsert.Append("\n");
sbInsert.Append("throw ex;");
sbInsert.Append("\n");
sbInsert.Append("}");
methodInsert.Statements.Add(new CodeSnippetStatement(sbInsert.ToString()));
methodInsert.Statements.Add(new CodeMethodReturnStatement(new CodeArgumentReferenceExpression("i")));
clsDecl.Members.Add(methodInsert);
#endregion
}
codeGenerator.GenerateCodeFromNamespace(cnsCodeDom, sw, cgo);
sw.Close();
codeFile.Close();
}
catch (Exception ex)
{
throw ex;
}
}
private string GenerateParameters(IEnumerable myList, int max)
{
StringBuilder sb = new StringBuilder();
sb.Append("(");
int i = 1;
foreach (Object obj in myList)
{
sb.Append(obj.ToString());
if (i < max)
sb.Append(", ");
i++;
}
sb.Append(")");
return sb.ToString();
}
private string GenerateParametersWithPrefix(IEnumerable myList, int max, string prefix)
{
StringBuilder sb = new StringBuilder();
sb.Append("(");
int i = 1;
int k = 0;
foreach (Object obj in myList)
{
string mno = dt3.Rows[k][1].ToString();
if (!IsNumericType(mno))
{
sb.Append("'");//
}
sb.Append(@"""");//
sb.Append("+");
sb.Append(prefix + "." + obj.ToString());
sb.Append("+");
sb.Append(@"""");
if (!IsNumericType(mno))
{
sb.Append("'");//
}
if (i < max)
sb.Append(", ");
i++;
k++;
}
sb.Append(")");
return sb.ToString();
}
private string GenerateParametersforSp(IEnumerable myList, int max, string prefix, DataTable dt)
{
StringBuilder sb = new StringBuilder();
int i = 1;
int k = 0;
foreach (Object obj in myList)
{
string mno = dt.Rows[k][1].ToString();
//if (!IsNumericType(mno))
//{
// sb.Append("'");//
//}
sb.Append(@"cmd.Parameters.AddWithValue(");//db.AddInParameter(dbCmd, "@AddressID", DbType.Int32, AddressID);
sb.Append(@"""");
sb.Append("@" + obj.ToString());
sb.Append(@"""");
sb.Append(",");
sb.Append(prefix + "." + obj.ToString());
sb.Append(");");
sb.Append("\n");
//sb.Append(@"""");
//if (!IsNumericType(mno))
//{
// sb.Append("'");//
//}
//if (i < max)
// sb.Append(", ");
i++;
k++;
}
return sb.ToString();
}
private bool IsNumericType(string strToCheck)
{
if (strToCheck == "int" || strToCheck == "bigint" || strToCheck == "binary" ||
strToCheck == "decimal" || strToCheck == "float" || strToCheck == "money" ||
strToCheck == "smallint" || strToCheck == "numeric" ||
strToCheck == "tinyint" || strToCheck == "smallmoney" || strToCheck == "varbinary")
{
return true;
}
return false;
}
protected void btnGenerateSP_Click(object sender, EventArgs e)
{
DataTable dtsp = new DataTable();
if (Session["dt"] != null)
dtsp = (DataTable)Session["dt"];
CSharpCodeProvider cscp = new CSharpCodeProvider();
Stream codeFile = File.Open("D:\\" + ddlTableName.SelectedValue.Trim() + "sp.txt", FileMode.Create);
StreamWriter sw = new StreamWriter(codeFile);
ICodeGenerator codeGenerator = cscp.CreateGenerator(sw);
CodeGeneratorOptions cgo = new CodeGeneratorOptions();
string comText = "select t.name as TABLE_NAME,c.name as COLUMN_NAME from sys.key_constraints as k join sys.tables as t on t.object_id = k.parent_object_id join sys.schemas as s on s.schema_id = t.schema_id join sys.index_columns as ic on ic.object_id = t.object_id and ic.index_id = k.unique_index_id join sys.columns as c on c.object_id = t.object_id and c.column_id = ic.column_id";
// This returns all the tablenames and their primarykey datafield names respetively
SqlDataAdapter da1 = new SqlDataAdapter(comText, con);
DataTable dt1 = new DataTable();
da1.Fill(dt1);
string primaryColName = string.Empty;
for (int i = 0; i < dt1.Rows.Count; i++)
{
string colName = dt1.Rows[i]["TABLE_NAME"].ToString();
if (colName == ddlTableName.SelectedItem.Value)
{
primaryColName = dt1.Rows[i]["COLUMN_NAME"].ToString();
break;
}
}
string tblName = ddlTableName.SelectedItem.Value;
string param = "obj" + tblName;
//ddlTableName.SelectedItem.Value = table name
// dt1 consists of 2 columns:all the tblNames in the selected database and Primary-key of that particular table
string comText1 = "SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tblName + "'";
SqlDataAdapter da2 = new SqlDataAdapter(comText1, con);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
dt3 = new DataTable();
dt3 = dt2.Copy();
// remove at position
string comt = @"select c.colid from sysindexes i join sysobjects o ON i.id = o.id join sysobjects pk ON i.name = pk.name AND pk.parent_obj = i.id
AND pk.xtype = 'PK' join sysindexkeys ik on i.id = ik.id and i.indid = ik.indid join syscolumns c ON ik.id = c.id AND ik.colid = c.colid
where o.name = '{0}' order by ik.keyno";
SqlCommand cmdIndex = new SqlCommand(string.Format(comt, tblName), con);
int colIndex = -1;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
colIndex = Convert.ToInt32(cmdIndex.ExecuteScalar());
}
catch (SqlException sqlEx)
{
throw sqlEx;
}
finally
{
con.Close();
}
//if (colIndex != -1)
//{
// colIndex = colIndex - 1;
// dt3.Rows.RemoveAt(colIndex);
//}
// This gives the no. of rows affected
int count = dt2.Rows.Count;
string[] colTypeList = new string[count];//
//This gives the list of Selected table column data types
string[] colNameList = new string[count];
// This gives the list of selected table column names
for (int i = 0; i < count; i++)
{
colTypeList[i] = dt2.Rows[i]["DATA_TYPE"].ToString();//
// dt2 table helps in finding the data type
colNameList[i] = dt2.Rows[i][0].ToString();
}
ArrayList pryRemove = new ArrayList();
//This gives the list of selected table column names list without primarykey column
for (int i = 0; i < count; i++)
{
if (colNameList[i] == primaryColName)
continue;
else
{
pryRemove.Add(colNameList[i]);
}
}
ArrayList pryAdd = new ArrayList();
//This gives the list of selected table column names list without primarykey column
for (int i = 0; i < count; i++)
{
pryAdd.Add(colNameList[i]);
}
#region storeprocedure for update
StringBuilder sbSP = new StringBuilder();
sbSP.Append(@"CREATE PROCEDURE sp_Update" +ddlTableName.SelectedItem);
sbSP.Append("\n");
sbSP.Append("(");
sbSP.Append(GenerateInputParametersforSp(pryAdd, pryAdd.Count, param, dt2));
sbSP.Append(")");
sbSP.Append("\n");
sbSP.Append("AS");
sbSP.Append("\n");
sbSP.Append("BEGIN");
sbSP.Append("\n");
sbSP.Append("Update " + ddlTableName.SelectedItem);
sbSP.Append("\n");
sbSP.Append("SET");
sbSP.Append("\n");
sbSP.Append(GenerateUpdatestatementforSp(pryAdd, pryAdd.Count, param, dt2));
sbSP.Append("\n");
sbSP.Append("Where ");
sbSP.Append(primaryColName + " = " + "@"+ primaryColName);
sbSP.Append("\n");
sbSP.Append("END");
sbSP.Append("\n");
sbSP.Append("Go");
CodeSnippetCompileUnit cscu = new CodeSnippetCompileUnit(sbSP.ToString());
codeGenerator.GenerateCodeFromCompileUnit(cscu, sw, cgo);
#endregion
#region storeprocedure for Insert
StringBuilder sbSP1 = new StringBuilder();
sbSP1.Append(@"CREATE PROCEDURE sp_Insert" + ddlTableName.SelectedItem);
sbSP1.Append("\n");
sbSP1.Append("(");
sbSP1.Append(GenerateInputParametersforSp(pryRemove, pryRemove.Count, param, dt3));
sbSP1.Append(")");
sbSP1.Append("\n");
sbSP1.Append("AS");
sbSP1.Append("\n");
sbSP1.Append("BEGIN");
sbSP1.Append("\n");
sbSP1.Append("Insert into " + ddlTableName.SelectedItem + "(");
sbSP1.Append("\n");
sbSP1.Append(GenerateInsertstatementforSp(pryRemove, pryRemove.Count, param, dt3));
sbSP1.Append(")");
sbSP1.Append("\n");
sbSP1.Append("values(");
sbSP1.Append("\n");
sbSP1.Append(GenerateInsertParamertesforSp(pryRemove, pryRemove.Count, param, dt3));
sbSP1.Append(")");
sbSP1.Append("\n");
sbSP1.Append("END");
sbSP1.Append("\n");
sbSP1.Append("Go");
CodeSnippetCompileUnit cscu1 = new CodeSnippetCompileUnit(sbSP1.ToString());
codeGenerator.GenerateCodeFromCompileUnit(cscu1, sw, cgo);
# endregion
#region storeprocedure for Get
StringBuilder sbSP2 = new StringBuilder();
sbSP2.Append(@"CREATE PROCEDURE sp_Get" + ddlTableName.SelectedItem);
sbSP2.Append("\n");
sbSP2.Append("AS");
sbSP2.Append("\n");
sbSP2.Append("BEGIN");
sbSP2.Append("\n");
sbSP2.Append("Select ");
sbSP2.Append(GenerateInsertstatementforSp(pryAdd, pryAdd.Count, param, dt2));
sbSP2.Append(" from " + ddlTableName.SelectedItem);
sbSP2.Append("\n");
sbSP2.Append("END");
sbSP2.Append("\n");
sbSP2.Append("Go");
CodeSnippetCompileUnit cscu2 = new CodeSnippetCompileUnit(sbSP2.ToString());
codeGenerator.GenerateCodeFromCompileUnit(cscu2, sw, cgo);
# endregion
#region storeprocedure for GetByPrimary
StringBuilder sbSP3 = new StringBuilder();
sbSP3.Append(@"CREATE PROCEDURE sp_Get" + ddlTableName.SelectedItem +"ByID");
sbSP3.Append("(@" + primaryColName + " int)");
sbSP3.Append("\n");
sbSP3.Append("AS");
sbSP3.Append("\n");
sbSP3.Append("BEGIN");
sbSP3.Append("\n");
sbSP3.Append("Select ");
sbSP3.Append(GenerateInsertstatementforSp(pryAdd, pryAdd.Count, param, dt2));
sbSP3.Append(" from " + ddlTableName.SelectedItem);
sbSP3.Append("\n");
sbSP3.Append("Where ");
sbSP3.Append(primaryColName + " = " + "@" + primaryColName);
sbSP3.Append("\n");
sbSP3.Append("END");
sbSP3.Append("\n");
sbSP3.Append("Go");
CodeSnippetCompileUnit cscu3 = new CodeSnippetCompileUnit(sbSP3.ToString());
codeGenerator.GenerateCodeFromCompileUnit(cscu3, sw, cgo);
# endregion
sw.Close();
codeFile.Close();
}
private string GenerateInputParametersforSp(IEnumerable myList, int max, string prefix, DataTable dt)
{
StringBuilder sb = new StringBuilder();
int i = 1;
int k = 0;
foreach (Object obj in myList)
{
string mno = dt.Rows[k][1].ToString();
sb.Append("@" + obj.ToString());
sb.Append(@" ");
if (mno == "nvarchar" || mno == "varchar")
{
sb.Append("nvarchar(50)");
}
else
{
sb.Append(mno);
}
if (i < max)
sb.Append(", ");
sb.Append("\n");
i++;
k++;
}
return sb.ToString();
}
private string GenerateUpdatestatementforSp(IEnumerable myList, int max, string prefix, DataTable dt)
{
StringBuilder sb = new StringBuilder();
int i = 1;
int k = 0;
foreach (Object obj in myList)
{
sb.Append(obj.ToString());
sb.Append(@"=");
sb.Append(@"@" + obj.ToString());
if (i < max)
sb.Append(", ");
sb.Append("\n");
i++;
k++;
}
return sb.ToString();
}
private string GenerateInsertstatementforSp(IEnumerable myList, int max, string prefix, DataTable dt)
{
StringBuilder sb = new StringBuilder();
int i = 1;
int k = 0;
foreach (Object obj in myList)
{
sb.Append(obj.ToString());
if (i < max)
sb.Append(", ");
sb.Append("\n");
i++;
k++;
}
return sb.ToString();
}
private string GenerateInsertParamertesforSp(IEnumerable myList, int max, string prefix, DataTable dt)
{
StringBuilder sb = new StringBuilder();
int i = 1;
int k = 0;
foreach (Object obj in myList)
{
sb.Append("@"+obj.ToString());
if (i < max)
sb.Append(", ");
sb.Append("\n");
i++;
k++;
}
return sb.ToString();
}
}
No comments:
Post a Comment