All database Function Class

Just copy and past all function in C# class. and save as clsConnection.cs in App_Code folder.


public String gSqlConnectionString = ConfigurationManager.AppSettings["gConnectionString"];
public SqlConnection gSqlConnection;
public SqlCommand gSqlCommand;
public DataSet gDataSet;
public SqlDataReader gSqlDataReader;
public SqlDataAdapter gSqlDataAdapter;
public DataTable gDataTable;
public Boolean bIsExists;
public String sID;


public void chkConnection()
{
if (gSqlConnection != null)
{
gSqlConnection.Close();
}

if (gSqlDataReader != null)
{
gSqlDataReader.Close();
}

}

public void dbUpdate(string v_sSql)
{
chkConnection();
gSqlConnection = new SqlConnection(gSqlConnectionString);
gSqlCommand = new SqlCommand(v_sSql, gSqlConnection);
gSqlConnection.Open();
gSqlCommand.ExecuteNonQuery();
gSqlConnection.Close();
}

public DataSet dbGetDateSet(string v_sSql)
{
chkConnection();
gSqlConnection = new SqlConnection(gSqlConnectionString);
gDataSet = new DataSet();
gSqlDataAdapter = new SqlDataAdapter(v_sSql, gSqlConnection);
gSqlDataAdapter.Fill(gDataSet);
return gDataSet;
}

public DataTable dbGetDataTable(string v_sSql)
{
chkConnection();
gSqlConnection = new SqlConnection(gSqlConnectionString);
gDataTable = new DataTable();
gSqlDataAdapter = new SqlDataAdapter(v_sSql, gSqlConnection);
gSqlDataAdapter.Fill(gDataTable);
return gDataTable;
}

public Boolean dbCheckData(string v_sSql)
{
chkConnection();
gSqlConnection = new SqlConnection(gSqlConnectionString);
gSqlCommand = new SqlCommand(v_sSql, gSqlConnection);
gSqlConnection.Open();
gSqlDataReader = gSqlCommand.ExecuteReader();
if (gSqlDataReader.Read())
{
bIsExists = true;
}
else {
bIsExists = false;
}
gSqlDataReader.Close();
gSqlConnection.Close();
return bIsExists;
}

public string dbGetID(string v_sSql)
{
chkConnection();
gSqlConnection = new SqlConnection(gSqlConnectionString);
gSqlCommand = new SqlCommand(v_sSql, gSqlConnection);
gSqlConnection.Open();
gSqlDataReader = gSqlCommand.ExecuteReader();
sID = string.Empty;
if (gSqlDataReader.Read())
{
sID = gSqlDataReader.GetValue(0).ToString();
}
gSqlDataReader.Close();
gSqlConnection.Close();
return sID;
}

Execute Procedures Class

This class have two procedure . both are used in insert, update and delete oeration.
First one function is used to perform insert, update and delete oeration.
second function is used to perform insert, update and delete oeration and return selected table.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
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;

///

/// Summary description for clsExecuteProcedures
///

public class clsExecuteProcedures
{
clsConnection conn = new clsConnection(); // connection class object
int iSizeParam;
int iCount;

public bool dbUpdate(SqlParameter[] v_arrParam, string v_sStoredProcedure,bool v_bIsExecute)
{
try
{
conn.gSqlConnection = new SqlConnection(conn.gSqlConnectionString);
conn.gSqlConnection.Open();
conn.gSqlCommand = new SqlCommand(v_sStoredProcedure, conn.gSqlConnection);
conn.gSqlCommand.CommandType = CommandType.StoredProcedure;

iSizeParam = v_arrParam.Length;

for (iCount = 0; iCount < iSizeParam; iCount++)
{
conn.gSqlCommand.Parameters.Add(v_arrParam[iCount]);
}

conn.gSqlCommand.ExecuteNonQuery();
conn.gSqlConnection.Close();

v_bIsExecute = true;
return v_bIsExecute;
}
catch
{
v_bIsExecute = false;
return v_bIsExecute;
}
}

// this function return select value from the database.
public DataTable dbUpdateReturnId(SqlParameter[] v_arrParam, string v_sStoredProcedure)
{
try
{
conn.gDataTable = new DataTable();
conn.gSqlDataAdapter = new SqlDataAdapter();
conn.gSqlConnection = new SqlConnection(conn.gSqlConnectionString);
conn.gSqlConnection.Open();
conn.gSqlCommand = new SqlCommand(v_sStoredProcedure, conn.gSqlConnection);
conn.gSqlCommand.CommandType = CommandType.StoredProcedure;

iSizeParam = v_arrParam.Length;
for (iCount = 0; iCount < iSizeParam; iCount++)
{
conn.gSqlCommand.Parameters.Add(v_arrParam[iCount]);
}

conn.gSqlDataAdapter.SelectCommand = conn.gSqlCommand;
conn.gSqlDataAdapter.Fill(conn.gDataTable);
conn.gSqlConnection.Close();
}
catch
{

return conn.gDataTable;

}
return conn.gDataTable;
}
}

Reading a text file to a remote PC

Put a file upload control in your page give id like "FileUpload1".
create a folder in your application like "TxtFile" folder name.
First save file in folder and then read the file
after reading delete the file.

StreamReader sr;
string sfileExtension = System.IO.Path.GetExtension(sFileName);
string sFileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.PostedFile.SaveAs(Server.MapPath("TxtFile") + "\\" + sFileName);

try
{
sr = new StreamReader(Server.MapPath("TxtFile") + "\\" + sFileName);

StringBuilder NameList = new StringBuilder();
NameList.Append(sr.ReadToEnd());
string info = NameList.ToString();
sr.Close();
File.Delete(Server.MapPath("TxtFile") + "\\" + sFileName);
}
catch (Exception ex)
{
Response.Write("File Reading Failed Due to : " + ex.Message.ToString());
}