You must Sign In to post a response.
  • Category: SQL Server

    How to connect to database

    want to connect my database into asp.net . need full code (iam a beginner)
  • #767247
    As you are beginner, you have to study ADO.NET for connecting your database and access the data. ADO.NET is taking care of Database in the .net. In ADO.NET you have to cover the following to get experience with it.
    1. SqlConnection
    2. SqlCommand
    3. SqlDataReader
    4. SqlDataAdapter
    5. DataSet (DataTable,DataRow,DataColumn)

    Following is the sample code which I am using "SqlDataReader" to get the data from the database.

    SqlConnection MyConnection = new SqlConnection("Your Connection String");
    SqlCommand TheCommandL = new SqlCommand("select Name from tblCustomer", MyConnection);
    MyConnection.Open();
    SqlDataReader rdr = null;
    rdr = TheCommandL.ExecuteReader();
    while (rdr.Read())
    {
    Console.Write(rdr["Cunm"].ToString());

    }

    By Nathan
    Direction is important than speed

  • #767249
    Hi

    You can follow this for connection string



    .NET Data Provider -- Default Relative Path -- Trusted Connection
    using System. Data. SqlClient;
    var conn = new SqlConnection();
    conn. ConnectionString =
    "Data Source=.\SQLExpress;" +
    "User Instance=true;" +
    "Integrated Security=true;" +
    "AttachDbFilename=|DataDirectory|DataBaseName.mdf;"
    conn. Open();




    Connection string from webconfig File



    <connectionStrings>
    <add name="CharityManagement"
    connectionString="Data Source=.;Initial Catalog=CharityManagement;Integrated Security=True"/>
    </connectionStrings>

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

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767256
    Hi,
    Hi,
    You can try this:
    Add SqlDatSource from toolbox to your page (suppose with id = "SqlDataSource2").
    Goto its properties--> ConnectionString--> Select <New connection..>
    Select Data source as Microsoft SQL Server and click Continue.
    Input the ServerName. After that select radio button 'Use Windows Authentication'.
    Then Select a database name and click on test Connection then click OK. After that click Finish.
    Now you can see the generated Connection String.
    Now coding part:

    SqlConnection con = new SqlConnection(SqlDataSource2.ConnectionString);
    con.Open();
    SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM TableName", con);
    DataTable dt = new DataTable();
    a.Fill(dt);
    con.Close();
    //... traverse dt for retrieved data.

  • #767270
    Use OLEDB or SQL provider and connect to database see below steps
    - Import System.Data.SQL and System.Data.SQLClient namespace
    - Declare connection object with SQLConnection class and pass connection string to it, connection string would ve as follows
    connetionString="Data Source=ServerName; Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
    follow below snippet for more details

    private void button1_Click(object sender, EventArgs e)
    {
    string connetionString = null;
    SqlConnection cnn ;
    connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
    cnn = new SqlConnection(connetionString);
    try
    {
    cnn.Open();
    MessageBox.Show ("Connection Open ! ");
    cnn.Close();
    }
    catch (Exception ex)
    {
    MessageBox.Show("Can not open connection ! ");
    }
    }

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #767287
    Hi

    Here you can find the complete example to retrieve data from database and showing in a gridview.

    public class employee
    {
    OleDbConnection cn=new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E://srid documents/mydocsssssss/products.accdb;User ID=admin");
    OleDbCommand cmd=new OleDbCommand();

    public DataSet getemploye(int id)
    {
    OleDbDataAdapter da = new OleDbDataAdapter("select * from emp where empid=" + id,cn);
    DataSet ds = new DataSet();
    da.Fill(ds,"p");
    return ds;
    }

    public bool checkemploye(int eno)
    {
    try
    {
    cmd.CommandText = "select count(*) from emp where empid=" + eno;
    cmd.Connection = cn;
    cn.Open();
    int i = (int)cmd.ExecuteScalar();
    if (i==1)
    {
    return true;
    }
    else
    return false;
    }
    catch (Exception ex)
    {
    throw new Exception(ex.Message);
    }
    finally
    {
    cn.Close();
    }
    }
    }

    In your button click write the below logic

    protected void Button1_Click(object sender, EventArgs e)
    {
    employee obj=new employee();
    bool b=obj.checkemploye(int.Parse(TextBox1.Text));
    if (b == true)
    {
    System.Data.DataSet ds = obj.getemploye(int.Parse(TextBox1.Text));
    GridView1.DataSource= ds.Tables[0];
    GridView1.DataBind();
    GridView1.Visible = true;
    }
    else
    {
    Response.Write("Emp id "+TextBox1.Text+" Doesnot exist");
    GridView1.Visible = false;
    }
    }

    Sridhar Thota.
    Editor: DNS Forum.

  • #767289
    Hi,

    If you want to connect with database and get the dynamic data then we have 2 options one is Providers and another one is ConnectionString. In providers we have an option called OLEDB Provider and in connections we have SQL connection / Oracle connections like that we have different types of connection strings available, if you want to get data from excel, csv or any other third party source then we will go for providers, if you want to connect directly to database then we will go for connection strings, based on your requirement we will change the connection type only rest of the part code is same as it is.

    this is the sample code for the same

    protected void BindData()
    {
    cmd = new SqlCommand("Get_Employees", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@DeptNo", DeptNo);
    dt = new DataTable();

    try
    {
    con.Open();
    da = new SqlDataAdapter(cmd);
    da.Fill(dt);
    if (dt.Rows.Count > 0)
    {
    GV.DataSource = dt;
    GV.DataBind();
    }

    }
    catch (Exception ex)
    {

    }
    finally
    {
    con.Close();
    con.Dispose();
    }
    }


    If you want to know more details about how to interact with database and get the data from database then refer below link this might be helpful to you..

    http://www.dotnetspider.com/resources/45044-Call-Stored-Procedure-Application.aspx

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments