Three Tier Insert and Retrieve Data(Using Singleton for SqlConnection only)

Application layer(PL) :- is the form where we design using the controls like textbox, labels, command buttons etc.

Business layer(BAL) :- is the class where we write the functions which get the data from the application layer and passes through the data access layer.BAL contains business logic, validations or calculations related with the data, if needed.

Data layer(DAL) :- is also the class which gets the data from the business layer and sends it to the database or gets the data from the database and sends it to the business layer.

Property layer(BE) :- is the sub layer of the business layer in which we make the properties to sent or get the values from the application layer. These properties help to sustain the value in a object so that we can get these values till the object destroy.

////// SQL HELPER CLASS(Inside the DAL Make sql Helper class) for connection string........

public class sqlHelper
{
private static SqlConnection connection = null;
public static SqlConnection conn
{
get
{
if (connection == null)
{
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolsConnectionString"].ConnectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}

return connection;
}
}

public static void closeconn(SqlConnection connection)
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}

}
}

///////// DAL(Data Access Layer) CLASS LIBRARY..............

Using BE; //ADD REFERENCE OF BE

public class DAL
{
public int InsertDal(BE.BE obBe)
{
try
{
SqlCommand cmd = new SqlCommand("Insert_Student", sqlHelper.conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("Name", obBe.Name);
cmd.Parameters.AddWithValue("Roll", obBe.Roll);
return cmd.ExecuteNonQuery();
}
catch
{
sqlHelper.closeconn(sqlHelper.conn);
throw;
}
finally
{
sqlHelper.closeconn(sqlHelper.conn);

}


}

public DataSet BindDal()
{
try
{
SqlCommand cmd = new SqlCommand("Bind", sqlHelper.conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
return ds;
}
catch
{
sqlHelper.closeconn(sqlHelper.conn);
throw;
}
finally
{
sqlHelper.closeconn(sqlHelper.conn);
}
}
}


/////////// BAL(Business Access Layer) CLASS LIBRARY....................
Using DAL;
Using BE;

public class BAL
{
DAL.DAL obDal = new DAL.DAL();
public int InsertBal(BE.BE obBe)
{
try
{
return obDal.InsertDal(obBe);
}
catch { throw; }
}

public DataSet BindBal()
{
try
{
return obDal.BindDal();

}
catch { throw; }

}
}

////// BE(Business Entity) CLASS LIBRARY.............(Property)

public class BE
{
private int _Id;
private string _Name = string.Empty;
private int _roll;

public int Id
{
get { return _Id; }
set { _Id = value; }
}
public string Name
{
get { return _Name; }
set { _Name = value; }
}
public int Roll
{
get { return _roll; }
set { _roll = value; }
}
}

/////////// PL(Persentation Layer).aspx Page
using BE;
using BAL;

BAL.BAL OBbAL = new BAL.BAL ();
BE.BE obBe = new BE.BE();
//Retrieve the value
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{

GridView1.DataSource = OBbAL.BindBal();
GridView1.DataBind();
}
}
//Insert the value
protected void btnInsert_Click(object sender, EventArgs e)
{
obBe.Name =TextBox1.Text.Trim().Replace(",","''");
obBe.Roll =Convert.ToInt32(TextBox2.Text.Trim().Replace(",","''"));
OBbAL.InsertBal(obBe);
TextBox1.Text = "";
TextBox2.Text = "";
}

Indexer in c#

//Indexer in c#

C# introduces a new concept known as Indexers which are used for treating an object as an array. The indexers are usually known as smart arrays in C# community.
Defining a C# indexer is much like defining properties. We can say that an indexer is a member that enables an object to be indexed in the same way as an array.

Indexer is a collection of SET and GET methods.
Indexer name must be "this".
One class can have only one indexer.
Indexer Concept is object act as an array.
Indexers in C# must have at least one parameter.
Else the compiler will generate a compilation error.
indexers are never static in C#.
Benefits:-
Indexers always need an object reference to assign or retreive data from arrays or collections.

The following is syntax

this [argument list]
{
get
{
// Get codes goes here
}
set
{
// Set codes goes here
}
}
//Example

class IndexerClass
{
private string[] names = new string[10];

public string this[int i]
{
get
{
return names[i];
}
set
{
names[i] = value;
}
}
}
static void Main(string[] args)
{
IndexerClass text = new IndexerClass();
text[0] = "subhash";
text[1] = "subhash1";
text[2] = "subhash2";
for (int i = 0; i < 10; i++)
{
Console.WriteLine(text[i]);
}
Console.ReadKey();
}

Create Xml File and Append the Existing Xml file

Using System.Xml;
Make one Cofiguration Folder Inside make that .xml file.

protected void btnGeneratexmlfile_Click(object sender, EventArgs e)
{
string type = "new";
Int32 queryid;
string FilePath = Request.PhysicalApplicationPath + @"Configuration\" + "QueryMaster.xml";
string SouPath = Request.PhysicalApplicationPath + "Configuration";
StringBuilder _UserInfo = new StringBuilder();

if (File.Exists(FilePath) == true)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load (FilePath);
XmlNode xmlNode = xmlDoc.SelectSingleNode("//querytrans");
if (type == "new")
queryid = 1000 + xmlNode.ChildNodes.Count + 1;
String Query = "trans type=\"new\" bookid=\"1\" assetid=\"35\" queryid=\"6\" byuserid=\"5\" byroleid=\"1\" touserid=\"4\" toroleid=\"9\" transdate=\"" + DateTime.Now.ToString() + "\"> ![CDATA[" + 12 + "]]> /trans>";
xmlNode.InnerXml = xmlNode.InnerXml + Query;

File.SetAttributes(FilePath, FileAttributes.Normal);
xmlDoc.Save(FilePath);//Append the xml file

}
else//if file not exist
{
_UserInfo.Append("?xml version=\"1.0\" encoding=\"utf-8\" ?>");
_UserInfo.Append("querytrans>");
_UserInfo.Append("/querytrans>");
StreamWriter _sw = new StreamWriter(SouPath + "\\QueryMaster.xml");
_sw.WriteLine(_UserInfo.ToString());
_sw.Flush();
_sw.Close();
_sw.Dispose();

}
}

HTML Report in c# .Net

//Blog does not take starting tag of the html.....please write all starting tag and end tag before each table tag,th tag and tr tag and td tag and closing tag...


protected void btnHtmlkReport_Click(object sender, EventArgs e)
{
conn.open();
SqlCommand cmd = new SqlCommand("select * from students", conn);
SqlDataReader oDr = cmd.ExecuteReader();
if (oDr.HasRows)
{
StringBuilder sb = new StringBuilder();
sb.Append("table align='center'>");
sb.Append("tr> td > th>Financial Report /th>/td>/tr>");
sb.Append("tr>td align='center'>");
sb.Append("id".ToString());
sb.Append("/td>");
sb.Append("td align='center'>");
sb.Append("Name".ToString());
sb.Append("/td>");
sb.Append("td align='center'>");
sb.Append("Roll".ToString());
// finish the rest of the row
sb.Append("");
// get your data
while (oDr.Read())
{
sb.Append("tr>td align='center'>");
sb.Append(oDr["id"].ToString());
sb.Append("/td>");
sb.Append("td align='center'>");
sb.Append(oDr["Name"].ToString());
sb.Append("/td>");
sb.Append("td align='center'>");
sb.Append(oDr["Roll"].ToString());
// finish the rest of the row
sb.Append("/td>/tr>");
}
sb.Append("/table>");
Response.Write(sb);
con.close();
}
}

Registered .DLL

Manual Registration
You can register a DLL manually. To do this, follow these steps:

1. Find the exact location of your .dll file.

2. Click on the Start menu.

3. Select the Run option.

4. On the edit field of the Run dialog box, type regsvr32 followed by the complete path and file name of the .dll file. The path and file name should be enclosed in quotation marks.

Here is an example:

regsvr32 “C:\Windows\System32\abd.dll”

5. Make sure you have typed the information correctly and press enter. The system will include the .dll file in the registry.

6. You will see a message box telling you the DLL file has been successfully registered.

Create and Close Connection using singleton in c#

//Class

public class GetConnection
{
private static SqlConnection connection = null;
public static SqlConnection conn
{
get {
if (connection == null)
{
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolsConnectionString"].ConnectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}

return connection;
}
}

public static void closeconn(SqlConnection connection)
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}

}

}

//aspx.cs Button click use Singleton Class

SqlCommand cmd = new SqlCommand("select * from students", GetConnection.conn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
SqlCommand cmd1 = new SqlCommand("insert into student(Name) values(s)", GetConnection.conn);
cmd.ExecuteNonQuery();
GetConnection.closeconn(GetConnection.conn);

Update Existing Xml file

//Xml File

?xml version="1.0" encoding="utf-8"?>
CategoryList>
Category ID="01">
MainCategory>XML /MainCategory>
Description>This is a list my XML articles. /Description>
Active>true /Active>
/Category>
Category ID="02">
MainCategory>Asp.net /MainCategory>
Description>Subahsh /Description>
Active>True /Active>
/Category>
/CategoryList>


//aspx.cs

if (!IsPostBack)
{
XmlDocument xml = new XmlDocument();
string s = Server.MapPath("XMLfILE.xml");
string list = "/CategoryList/Category";
xml.Load(s);
XmlNodeList xn = xml.SelectNodes(list);
foreach (XmlNode eleSourceNode in xn)
{
string ID = eleSourceNode.Attributes["ID"].Value;
string author1 = eleSourceNode["MainCategory"].InnerText;
DropDownList1.Items.Add(new ListItem(author1, ID));
}


}

//DropDown SelectIndexChange

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(Server.MapPath("XMLfILE.xml"));

XmlNodeList nodeList = xmlDoc.SelectNodes("/CategoryList/Category[@ID='"+DropDownList1.SelectedValue+"' ]");

TextBox2.Text = nodeList[0].ChildNodes[0].InnerText;
TextBox3.Text= nodeList[0].ChildNodes[1].InnerText;
TextBox4.Text = nodeList[0].ChildNodes[2].InnerText;

}

//Update Xml File

protected void btnUpdateXml_Click(object sender, EventArgs e)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(Server.MapPath("XMLfILE.xml"));

XmlNodeList nodeList = xmlDoc.SelectNodes("/CategoryList/Category[@ID='" + DropDownList1.SelectedValue + "' ]");

// update MainCategory
nodeList[0].ChildNodes[0].InnerText = TextBox2.Text.Trim();
// update Description
nodeList[0].ChildNodes[1].InnerText = TextBox3.Text.Trim();
// update Active
nodeList[0].ChildNodes[2].InnerText = TextBox4.Text.Trim();

// Don't forget to save the file
xmlDoc.Save(Server.MapPath("XMLfILE.xml"));
}