Add Delete modify the Column in Database

Before Add the Column in the table need to check column already exists or not

If Not Exists(select * from sys.columns where Name = N'ArabicName' and Object_ID = Object_ID(N'Branch'))
Alter Table [Branch] Add ArabicName nvarchar(500)
Go

Drop the Coloumn from the table

ALTER TABLE tbl1 Drop Column abcd;


Modify the coulmn size from the existing table
ALTER TABLE tbl1 Alter Column abcd nvarchar(4000) not null;

Render Codein aspx page using XML and XSL

Once you have created your template (Xml,Xslt) then you have to render on the page for render the code in your page you have to create one of the place holder in your page and place form load code in you load section.

You must need to modify your xml,xsl file name.



protected void Page_Load(object sender, EventArgs e)
{
XslCompiledTransform proc = new XslCompiledTransform();
proc.Load(Server.MapPath("XSLTFile.xsl"));
StringWriter writer = new StringWriter();
proc.Transform(XmlReader.Create(Server.MapPath("form.xml")), null, writer);
P1.Controls.Add(Page.ParseControl(writer.ToString()));
}

Some time Asp.net controls doesn't placed in the pages using xslt so you have to apply the above format or lines or code other wise you can directly render the code using this control

Column level query OR Check Column exists in table

Check the column is already exist or not, on alter the table, During the add a new column.


if Not exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tblMurli'
and COLUMN_NAME='My_NewColumn' )
Alter table tblMurli Add My_NewColumn numeric(18, 0)

List Generic Controls

public class Product
{
public Product()
{
//
// TODO: Add constructor logic here
//
}
private int number;
public int Number
{
get { return number; }
}

private string name;
public string Name
{
get { return name; }
}

public Product(int number, string name)
{
this.name = name;
this.number = number;
}
}

===============================================
// List
List list = new List();
list.Add(new Product(1,"Murli"));
list.Add(new Product(2,"Sanjay"));
list.Add(new Product(3,"Mahesh"));
list.Add(new Product(4,"Tom"));


List intlist = new List();
intlist.Add(1);
intlist.Add(2);
intlist.Add(5);
intlist.Add(7);

for (int i = 0; i < intlist.Count; i++)
{
lblMessage.Text += "
No " + i + " :" + intlist[i];
}

foreach (int layland in intlist)
{
lblMessage.Text += layland;
}


GridView1.DataSource = intlist;
GridView1.DataBind();

Write Xml File Via Sql

Comment everything from the aspx page and write below code in the code behind files

1.==================================
string Con = "Data Source="46\\SQLEXPRESS2008;User id=sa;password=murli;Initial Catalog=Murli";
XmlDocument xDoc = new XmlDocument();
XPathNavigator xPath = xDoc.CreateNavigator();
using (SqlConnection Conn = new SqlConnection(Con))
{
Conn.Open();
SqlCommand Cmd = new SqlCommand("Select * from Product As Products For Xml Auto,Elements", Conn);
using (XmlWriter xW = xPath.PrependChild())
{
xW.WriteStartElement("Products");
using (XmlReader xR = Cmd.ExecuteXmlReader())
{
xW.WriteNode(xR, true);
}
xW.WriteEndElement();
}
}
Response.ContentType = "text/xml";
xDoc.Save(Response.Output);

2.=================================
using System.IO;
using System.Text;
using System.Data.SqlClient;


protected void Button1_Click(object sender, EventArgs e)
{
string str = "";
SqlConnection con = new SqlConnection("Data Source=MyDataSource\\SQLEXPRESS2008;Initial Catalog=Murli;User ID=sa;Password=murli");
con.Open();
string s = "select * from tblAnswer";
SqlCommand cmd = new SqlCommand(s, con);
SqlDataReader dr = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
sb.Append("");
sb.Append("");
while (dr.Read())
{
sb.Append("");
sb.Append("" + dr[0].ToString() + "");
sb.Append("" + dr[1].ToString() + "");
sb.Append("");
}
sb.Append("");

StreamWriter stw = File.CreateText("D://mutest1.xml");
stw.WriteLine(sb.ToString());
stw.Close();
}

Read XML File from Javascript

For reading xml data using javascript we need to go to the following steps.
1. First create xml file File name called book.xml or anything else.
2. on the another file write the below code
============================book.xml=============================================
<?xml version="1.0" encoding="utf-8" ?>
<bookstore>
<book category="cooking">
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="children">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="web">
<title lang="en">XQuery Kick Start</title>
<author>James McGovern</author>
<author>Per Bothner</author>
<author>Kurt Cagle</author>
<author>James Linn</author>
<author>Vaidyanathan Nagarajan</author>
<year>2003</year>
<price>49.99</price>
</book>
<book category="web" cover="paperback">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>
==================================================
================Page where want to display the xml data =============================
<head>
<script type="text/javascript">
function loadXmlData(FName)
{
try{ // Internet Explorer
xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
}
catch(e){
try{ // Mozila,Safari,Opera,etc
xmlDoc = document.implementation.createDocument("","",null);
}
catch(e){alert(e.message);}
}

try{
xmlDoc.async = false;
xmlDoc.load(FName);
return xmlDoc;
}
catch(e){alert(e.message);}
}

function get_firstChild(n)
{
y=n.firstChild;
while (y.nodeType!=1)
{
y=y.nextSibling;
}
return y;
}
</script>
</head>

<body>
<script type="text/javascript">
xmlDoc=loadXmlData("books.xml");
var Count = xmlDoc.getElementsByTagName("title");
//alert(Count.length);
for(var z=0;z<Count.length-1;z++)
{
document.write(z+1+" "+Count[z].childNodes[0].nodeValue +"<br/>");
}


document.write("<hr/>");
document.write(xmlDoc.getElementsByTagName("title")[0].childNodes[0].nodeValue);
document.write("<br />");
document.write(xmlDoc.getElementsByTagName("author")[0].childNodes[0].nodeValue);
document.write("<br />");
document.write(xmlDoc.getElementsByTagName("year")[0].childNodes[0].nodeValue);

x=get_firstChild(xmlDoc.getElementsByTagName("book")[3]);
document.write(x.nodeName);
</script>
</body>

==================Output=====================
1 Everyday Italian
2 Harry Potter
3 XQuery Kick Start
<hr/>
Everyday Italian
Giada De Laurentiis
2005title

Execute Sql stmt using procedure

EXEC sp_executesql N'SELECT * FROM tblFirst'