Programming in almost language

This is the site where you may share your knowledge and experience to eachother..

  • Categories

  • LinkedIn

  • Tweet Me

  • My footsteps

Archive for the ‘Connection Strings’ Category

Global Connection Strings

Posted by Praveen Kumar on March 8, 2008

Here i am going to describe how you can create a global connection for all pages.
as i have already describe in my web.config and global.asax(please refer it to know more) category
This is my web.config file where i may store number of connection.
Here i create two connetion with ODBC driver and SQL Server driver

 web.config

<configuration>
  <connectionStrings>
    <add name=”mySqlConn” connectionString=”Driver={MySQL ODBC 5.1 Driver};Server=192.168.2.140;Database=payroll_master;Uid=praveen;”/>
    <add name=”sqlConnString” connectionString=”Driver={SQL Server};Server=127.0.0.1;database=mydatabase;Uid=sa;”/>
  </connectionStrings>
  <appSettings>
    <!–<add key=”myConn” value=”provider=MySQL ODBC 5.1 Driver;server=192.168.2.140;database=payroll_master;User Id=praveen;”/>–>
  </appSettings>

and this is my seperate class file named ConnectionClass which use connectionstrings defined in web.config file

ConnectionClass.cs 

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;

public class ConnectionClass : System.Web.UI.Page
{
    OdbcConnection conn;
     public ConnectionClass()
 {
 }
   public OdbcConnection Connect_thru_config()
    {
        string connString;
//If you want to create MySQL connection then you need to pass only mySqlConn
 //connString = System.Configuration.ConfigurationManager.ConnectionStrings[“mySqlConn”].ConnectionString;
        connString = System.Configuration.ConfigurationManager.ConnectionStrings[“sqlConnString”].ConnectionString;//for Sql
        OdbcConnection conwithfig = new OdbcConnection(connString);
        return conwithfig;
     }
}

and this is the ConnectionDemo.aspx.cs page where i am calling Connect_thru_config()

ConnectionDemo.aspx.cs 

try
        {
            ConnectionClass conobj = new ConnectionClass();
            configcn = conobj.Connect_thru_config();
            configcn.Open();
            DataSet ds = new DataSet();
            string sql = “select *from FK_TBLUSER”;
            OdbcDataAdapter da = new OdbcDataAdapter(sql, configcn);
            da.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
        } catch (Exception e1)     {       Page.Response.Write(“error” + e1);       }
        finally        {            configcn.Close();        }
    }

Conclusion–

This article describes that you may have number of connection in config file and call these connection strings in a seperate class file in a method, and you may use this class anywhere in your project. you do not need to write connection string in each page.
I hope this articles may help you. If you want to give any suggestion please you can leave your comment to improve this article.
your comments and suggestion will be most appreciable.

Advertisements

Posted in Connection Strings | Leave a Comment »

Connectivity between excel and C#

Posted by Praveen Kumar on February 18, 2008

This code shows the connectivity with Excel to C#

Description–

Here we have one excelsheet which contains no of sheets and going to import on page and export to database. we have 4 dropdownlist box named valueDrpDwn(Sheet name) empnoDrpDwn(EmpNo),remarksDrpDwn(Sex) joindateDrpdwnList1(joinDate)

protected void Button1_Click(object sender, EventArgs e)
{
string fileUpload = (System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName));
string temp = “D:/”;

temp += FileUpload1.FileName;
TextBox1.Text = temp;
DataTable dt = new DataTable();
DataSet ds = new DataSet();
OleDbConnection connection = new OleDbConnection(“provider=Microsoft.Jet.OLEDB.4.0;data source=” + TextBox1.Text + “;Extended Properties=\”Excel 5.0;HDR=NO;IMEX=1;\””);
OleDbDataReader myReader;
OleDbCommand mycmd;
connection.Open();
string query = “SELECT * FROM [” + sheetDrpDwn.SelectedValue + “$A1:M1]”;
mycmd = new OleDbCommand(query, connection);
myReader = mycmd.ExecuteReader();
valueDrpDwn.Items.Clear();
empnoDrpDwn.Items.Clear();
remarksDrpDwn.Items.Clear();
joindateDrpDwnList1.Items.Clear();

while (myReader.Read())
{
for (int i = 0; i < myReader.FieldCount; i++)
{
valueDrpDwn.Items.Add(myReader[i].ToString());//this dropdown is for Name
empnoDrpDwn.Items.Add(myReader[i].ToString());
remarksDrpDwn.Items.Add(myReader[i].ToString());// this dropdown is for gender
joindateDrpDwnList1.Items.Add(myReader[i].ToString());
}
}
myReader.Close();
}

protected void import_Click(object sender, EventArgs e)
{
myconnection1.Open();
OleDbConnection ExcelConnection = new OleDbConnection(“provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + TextBox1.Text + “;Extended Properties=Excel 8.0;”);
ExcelConnection.Open();
OleDbCommand ExcelCommand = new OleDbCommand(
“INSERT INTO [ODBC;Driver={MySql ODBC 5.1 Driver};Server=192.168.2.140;Database=yodlee_dbo;UID=praveen;Trusted_ Connection=yes].[TBLEMPLOYEE1] ” +
“(EMPLOYEENO,NAME,SEX) ” +
“SELECT [” + empnoDrpDwn.Text.ToString() + “], [” + valueDrpDwn.Text.ToString() + “],[” + remarksDrpDwn.Text.ToString() + “]” +
“FROM [Sheet1$] ” + “;”, ExcelConnection);
OdbcCommand ToTblCmd = new OdbcCommand(“insert TBLEMPLOYEE(EMPLOYEENO,NAME,SEX) select EMPLOYEENO,NAME,SEX from TBLEMPLOYEE1”, myconnection1);
OdbcCommand DelTblCmd = new OdbcCommand(“delete from TBLEMPLOYEE1”, myconnection1);
ExcelCommand.CommandType = CommandType.Text;
ExcelCommand.ExecuteNonQuery();
ToTblCmd.CommandType = CommandType.Text;
ToTblCmd.ExecuteNonQuery();
DelTblCmd.CommandType = CommandType.Text;
DelTblCmd.ExecuteNonQuery();
Page.Response.Write(“Congrats Data inserted”);
ExcelConnection.Close();

Posted in Connection Strings | 1 Comment »