Master in ASP.Net And Sql: December 2016

In Page Design
========================================
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="TestReansaction.aspx.cs" Inherits="WebApplication1.TestReansaction" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />

<asp:Button ID="button2" runat="server" Text="btn test 2" OnClick="button2_Click" />

</div>

</form>
</body>
</html>

=================================

In C# Code

====================

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace WebApplication1

{

public partial class TestReansaction : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

using (CompanyEntities context = new CompanyEntities())

{

using (var transaction = context.Database.BeginTransaction())

{

try

{

EmployeeMaster employee = new EmployeeMaster();

employee.Code = "A0001";

employee.Name = "Jignesh Trivedi";

employee.DepartmentId =2;

context.EmployeeMasters.Add(employee);

context.SaveChanges();

DepartmentMaster dept = new DepartmentMaster();

dept.Code = "DEP000112765776767767676";

dept.Name = "Department 1";

context.DepartmentMasters.Add(dept);

context.SaveChanges();

transaction.Commit();

}

catch (Exception ex)

{

transaction.Rollback();

}

In WebConfig First Set The Connection String.
======================================

<?xml version="1.0"?>



<configuration>
<connectionStrings>
<add name="InterviewConnectionString" connectionString="Data Source=SATYABRATA;Initial Catalog=Interview;Persist Security Info=True;User ID=satya;Password=satya@215;MultipleActiveResultSets=True;Application Name=EntityFramework"
providerName="System.Data.SqlClient" />

</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
</system.web>

</configuration>
======================================

Design Page in Source FIle
======================================
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Schema.aspx.cs" Inherits="CreateaSchema.Schema" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TxtSchema" runat="server" placeholder="Put Schema Name"></asp:TextBox>
<br />
<br />
<asp:Button ID="BtnCreate" runat="server" Text="CreateSchematable" OnClick="BtnCreate_Click" />
<asp:Button ID="BtnDelete" runat="server" Text="DeleteSchematable" OnClick="BtnDelete_Click" />
</div>
</form>
</body>

</html>

=======================================

In C# Code Write This

=========================================

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace CreateaSchema

{

public partial class Schema : System.Web.UI.Page

{

DataTable schemaTable;

SqlCommand cmd1;

SqlDataReader dtp;

List<string> tables;

DataTable dt;

string schemaname;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

}

}

protected void BtnCreate_Click(object sender, EventArgs e)

{

if (TxtSchema.Text != "")

{

#region Get Connection String

var connectionString = ConfigurationManager.ConnectionStrings["InterviewConnectionString"].ConnectionString;

var csb = new SqlConnectionStringBuilder(connectionString);

string DataSource = csb.DataSource;

#endregion

#region Get All Tables

SqlConnection objConn = new SqlConnection(connectionString);

objConn.Open();

tables = new List<string>();

DataTable dt = objConn.GetSchema("Tables");

foreach (DataRow row in dt.Rows)

{

string tablename = (string)row[2];

tables.Add(tablename);

}

#endregion

#region Create Schema

foreach (DataRow row in dt.Rows)

{

string schemaname = (string)row[1];

if (schemaname == TxtSchema.Text)

{

Response.Write("<script>alert('Schema Already exist !');</script>");

return;

}

}

string schema = TxtSchema.Text;

string schema1 = "CREATE SCHEMA " + schema;

SqlCommand cmd = new SqlCommand(schema1, objConn);

cmd.ExecuteNonQuery();

#endregion

#region Create Schema table

foreach (var item in tables)

{

string ty = "SELECT * FROM " + item;

cmd1 = new SqlCommand(ty, objConn);

dtp = cmd1.ExecuteReader(CommandBehavior.KeyInfo);

schemaTable = dtp.GetSchemaTable();

string tyr = CreateTABLE(item, schemaTable, schema);

SqlCommand cmd3 = new SqlCommand(tyr, objConn);

cmd3.ExecuteNonQuery();

}

#endregion

TxtSchema.Text = "";

}

}

public static string CreateTABLE(string tableName, DataTable table, string schema)

{

var connectionString = ConfigurationManager.ConnectionStrings["InterviewConnectionString"].ConnectionString;

SqlConnection con = new SqlConnection(connectionString);

string sqlsc;

sqlsc = "CREATE TABLE " + schema + "." + tableName + "(";

for (int i = 0; i < table.Rows.Count; i++)

{

sqlsc += "\n [" + table.Rows[i]["ColumnName"] + "] ";

string columnType = table.Rows[i]["DataType"].ToString();

switch (columnType)

{

case "System.Int32":

sqlsc += " int ";

break;

case "System.Int64":

sqlsc += " bigint ";

break;

case "System.Int16":

sqlsc += " smallint";

break;

case "System.Byte":

sqlsc += " tinyint";

break;

case "System.Decimal":

sqlsc += " decimal ";

break;

case "System.DateTime":

sqlsc += " datetime ";

break;

case "System.String":

default:

sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());

break;

}

if (table.Columns[i].AutoIncrement)

sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";

if (!table.Columns[i].AllowDBNull)

sqlsc += " NOT NULL ";

sqlsc += ",";

}

return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";

}

protected void BtnDelete_Click(object sender, EventArgs e)

{

if (TxtSchema.Text != "")

{

#region Get Connection String

var connectionString = ConfigurationManager.ConnectionStrings["InterviewConnectionString"].ConnectionString;

var csb = new SqlConnectionStringBuilder(connectionString);

string DataSource = csb.DataSource;

#endregion

#region Get And Delete Schema

SqlConnection objConn = new SqlConnection(connectionString);

objConn.Open();

tables = new List<string>();

dt = objConn.GetSchema("Tables");

foreach (DataRow row in dt.Rows)

{

string tablename = (string)row[2];

tables.Add(tablename);

}

foreach (DataRow row in dt.Rows)

{

schemaname = (string)row[1];

if (schemaname == TxtSchema.Text)

{

string Item = (string)row[2];

string query = "DROP TABLE " + schemaname + "." + Item;

SqlCommand cmd3 = new SqlCommand(query, objConn);

cmd3.ExecuteNonQuery();

}

}

string tyu = "DROP SCHEMA " + TxtSchema.Text;

SqlCommand cmd4 = new SqlCommand(tyu, objConn);

cmd4.ExecuteNonQuery();

#endregion

TxtSchema.Text = "";

}

}

}

}

==========================================

Master in ASP.Net And Sql

Sunday, 18 December 2016

How to set Transaction and Roll Back using Entity Framework in C# Code.

Create Schema Using C# Asp .Net Dynamically.