Tuesday, 30 October 2012
gridview
GridViewWithInsertUpdateDelete:
gridview.aspx:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
<style type="text/css">
.Gridview
{
font-family:Verdana;
font-size:10pt;
font-weight:normal;
color:black;
}
</style>
<script type="text/javascript">
function ConfirmationBox(username) {
var result = confirm('Are you sure you want to delete '+username+' Details?' );
if (result) {
return true;
}
else {
return false;
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvDetails" DataKeyNames="UserId,UserName" runat="server"
AutoGenerateColumns="false" CssClass="Gridview" HeaderStyle-BackColor="#61A6F8"
ShowFooter="true" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White"
onrowcancelingedit="gvDetails_RowCancelingEdit"
onrowdeleting="gvDetails_RowDeleting" onrowediting="gvDetails_RowEditing"
onrowupdating="gvDetails_RowUpdating"
onrowcommand="gvDetails_RowCommand">
<Columns>
<asp:TemplateField>
<EditItemTemplate>
<asp:ImageButton ID="imgbtnUpdate" CommandName="Update" runat="server" ImageUrl="~/Images/update.jpg" ToolTip="Update" Height="20px" Width="20px" />
<asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/Cancel.jpg" ToolTip="Cancel" Height="20px" Width="20px" />
</EditItemTemplate>
<ItemTemplate>
<asp:ImageButton ID="imgbtnEdit" CommandName="Edit" runat="server" ImageUrl="~/Images/Edit.jpg" ToolTip="Edit" Height="20px" Width="20px" />
<asp:ImageButton ID="imgbtnDelete" CommandName="Delete" Text="Edit" runat="server" ImageUrl="~/Images/delete.jpg" ToolTip="Delete" Height="20px" Width="20px" />
</ItemTemplate>
<FooterTemplate>
<asp:ImageButton ID="imgbtnAdd" runat="server" ImageUrl="~/Images/AddNewitem.jpg" CommandName="AddNew" Width="30px" Height="30px" ToolTip="Add new User" ValidationGroup="validaiton" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="UserName">
<EditItemTemplate>
<asp:Label ID="lbleditusr" runat="server" Text='<%#Eval("Username") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblitemUsr" runat="server" Text='<%#Eval("UserName") %>'/>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrusrname" runat="server"/>
<asp:RequiredFieldValidator ID="rfvusername" runat="server" ControlToValidate="txtftrusrname" Text="*" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<EditItemTemplate>
<asp:TextBox ID="txtcity" runat="server" Text='<%#Eval("City") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblcity" runat="server" Text='<%#Eval("City") %>'/>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrcity" runat="server"/>
<asp:RequiredFieldValidator ID="rfvcity" runat="server" ControlToValidate="txtftrcity" Text="*" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Designation">
<EditItemTemplate>
<asp:TextBox ID="txtstate" runat="server" Text='<%#Eval("Designation") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblstate" runat="server" Text='<%#Eval("Designation") %>'/>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtftrDesignation" runat="server"/>
<asp:RequiredFieldValidator ID="rfvdesignation" runat="server" ControlToValidate="txtftrDesignation" Text="*" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
<div>
<asp:Label ID="lblresult" runat="server"></asp:Label>
</div>
</form>
</body>
</html>
gridview.aspx.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
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;
public partial class _Default : System.Web.UI.Page
{
private SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindEmployeeDetails();
}
}
protected void BindEmployeeDetails()
{
con.Open();
SqlCommand cmd = new SqlCommand("Select * from Employee_Details", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetails.DataSource = ds;
gvDetails.DataBind();
int columncount = gvDetails.Rows[0].Cells.Count;
gvDetails.Rows[0].Cells.Clear();
gvDetails.Rows[0].Cells.Add(new TableCell());
gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetails.Rows[0].Cells[0].Text = "No Records Found";
}
}
protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
BindEmployeeDetails();
}
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Value.ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();
TextBox txtcity = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtcity");
TextBox txtDesignation = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtstate");
lblresult.ForeColor = Color.Green;
lblresult.Text = username + " Details Updated successfully";
gvDetails.EditIndex = -1;
BindEmployeeDetails();
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetails.EditIndex = -1;
BindEmployeeDetails();
}
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["UserId"].ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();
con.Open();
SqlCommand cmd = new SqlCommand("delete from Employee_Details where UserId=" + userid, con);
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
BindEmployeeDetails();
lblresult.ForeColor = Color.Red;
lblresult.Text = username + " details deleted successfully";
}
}
protected void gvDetails_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//getting username from particular row
string username = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "UserName"));
//identifying the control in gridview
ImageButton lnkbtnresult = (ImageButton)e.Row.FindControl("imgbtnDelete");
//raising javascript confirmationbox whenver user clicks on link button
if (lnkbtnresult != null)
{
lnkbtnresult.Attributes.Add("onclick", "javascript:return ConfirmationBox('" + username + "')");
}
}
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if(e.CommandName.Equals("AddNew"))
{
TextBox txtUsrname = (TextBox)gvDetails.FooterRow.FindControl("txtftrusrname");
TextBox txtCity = (TextBox)gvDetails.FooterRow.FindControl("txtftrcity");
TextBox txtDesgnation = (TextBox) gvDetails.FooterRow.FindControl("txtftrDesignation");
con.Open();
SqlCommand cmd =
new SqlCommand(
"insert into Employee_Details(UserName,City,Designation) values('" + txtUsrname.Text + "','" +
txtCity.Text + "','" + txtDesgnation.Text + "')", con);
int result= cmd.ExecuteNonQuery();
con.Close();
if(result==1)
{
BindEmployeeDetails();
lblresult.ForeColor = Color.Green;
lblresult.Text = txtUsrname.Text + " Details inserted successfully";
}
else
{
lblresult.ForeColor = Color.Red;
lblresult.Text = txtUsrname.Text + " Details not inserted";
}
}
}
}
ASP.NET
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();
}
}
Subscribe to:
Comments (Atom)