How to Establish Connection string With Insert Records from C#,.Vb,MVC Different Approcah

Through-out the article we will learn step-by-step: How to perform Establish Connection string Dotnet Framework(C#,Vb.net,MVC). We will discuss. How to Connect the Database then Retrieve the Data and Binding Gridview Controls (Asp.net,C#.MVC).

Abstract

Through-out the article we will learn step-by-step: How to perform Establish Connection string Dotnet Framework.

We will discuss How to Connect the Database then Retrieve the Data and Binding Gridview Controls (C#,Vb.net,MVC) of pure Sql server

Introduction

In these days, Dotnet framework is most growing framework and as a developer I am curious to know and learn about the new features of this framework.

From last couple of days I was wondering how can I perform Establish Connection string Dotnet Framework.

We will discuss How to Connect the Database then Retrieve the Data and Binding Gridview Controls (Asp.net,C#.MVC) of pure Sql server. For the same I have gone through various forums etc. Unfortunately, I did saw very few posts/articles for the same.

Finally, I tried and came with a solution to perform Connection string Establish operations from Server side . In this article, I am going to share my findings with all of you.

Why Step-by-Step

Ah! This question came to my mind while I was finding the solution of my problem, I jotted down few points and finally called them as a steps to perform Connection string from connect the database Operations.

Pre-requisite

To implement the solution and to feel the taste of code, you should:
<ul>
<li>Have Visual Studio 2013 or later with the support of Dotnet</li>
<li>Have Basic Idea of SQL Server</li>
<li>Have Basic Idea of Sql queries</li>
</ul>

<h1>What we are waiting for?</h1>
In this Article I have explain different type of connection string we can access db differed Approch

Web Application - C#,Sql -> Static and Stored Procedure.
<h1>This code Implemented c# code for Connect db and fetch the records and binding the
web application Control</h1>


 string connectionstring = "Data source=PCNAME; Initial Catalog=Test; Integrated Security=true";
                SqlConnection conn = new SqlConnection(connectionstring);
                conn.Open();
                SqlCommand comm = new SqlCommand("SELECT * from tst111 where studid=1123", conn);
                SqlDataAdapter da = new SqlDataAdapter(comm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                Grd1.DataSource = ds;
                Grd1.DataBind();
  protected void Btsubmit_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(connectionstring);
            conn.Open();
            SqlCommand comm = new SqlCommand("Insert into StudentEnq values("+ TxtStudId.Text + ",'"+  TxtName.Text + "')",conn);
            int Result=comm.ExecuteNonQuery();
        }

        protected void Submit_Click(object sender, EventArgs e)
        {

            SqlConnection con = new SqlConnection(connectionstring);
            SqlCommand cmd = new SqlCommand("AddStudInfo", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@StudId", SqlDbType.Int).Value = Convert.ToInt32(TxtStudId.Text);
            cmd.Parameters.Add("@studname", SqlDbType.VarChar, 50).Value = TxtName.Text.ToString();
            con.Open();
            int Result=cmd.ExecuteNonQuery();
            con.Close();
        }

Html Design page this


<table>
      <tr>
          <td>
              Student ID
          </td>
          <td>
             <asp:TextBox ID="TxtStudId" runat ="server"></asp:TextBox>
          </td>
      </tr>
      <tr>
          <td>
              Student Name
          </td>
          <td>
             <asp:TextBox ID="TxtName" runat ="server"></asp:TextBox>
          </td>
      </tr>
      <tr>
          <td colspan="2">
              <asp:Button ID="Btsubmit" runat ="server" Text ="Submit" OnClick="Btsubmit_Click" />
              <asp:Button ID="Submit" runat ="server" Text ="Stored Proc" OnClick="Submit_Click" />
          </td>
      </tr>
  </table>

<h1>How to use connection string from webconfig</h1>
WebConfig


<connectionStrings>
<add name="TestConnectionString" connectionString="Data Source=PCNAME;Initial Catalog=Test;Integrated Security=True" providerName="System.Data.SqlClient" >
</connectionStrings>

<h1>How to Establish Connection string from Console Application </h1>


Console Application

SqlConnection MyConn = new SqlConnection("server=PCNAME;database=Test;Integrated Security=SSPI");
SqlCommand command = MyConn.CreateCommand();
 MyConn.Open();
                 command = new SqlCommand("SELECT * FROM TblEmp", MyConn);
                 using (SqlDataReader reader = command.ExecuteReader())
                 {
                     Console.WriteLine("Emp Id\t\t\t\t\t Name \t\t\t\t\t\t EmpNo\t");
                     while (reader.Read())
                     {
                         Console.WriteLine(String.Format("{0} \t\t\t | {1} \t | {2}",
                             reader[0], reader[1], reader[2]));
                     }
                 }
                 Console.WriteLine("Data displayed! Now press enter to move to the next section!");
                 Console.ReadLine();
                 Console.Clear();
                 MyConn.Close();

<h1>How to Establish Connection string from VB.net Web Application </h1>


Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
    Inherits System.Web.UI.Page
    Dim Sqlcon As New SqlConnection("Data Source=PCNAME;Initial Catalog=Test;Integrated Security=True")
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Sqlcon.Open()
        Dim dt As New DataTable
        Dim sqladp As New SqlDataAdapter("Select * from tbltmp", Sqlcon)
        sqladp.Fill(dt)
    End Sub
End Class

    Protected Sub Btsubmit_Click(sender As Object, e As EventArgs)
        Sqlcon.Open()
        Dim sqlcmd As New SqlCommand("Insert into StudentEnq values(" + TxtStudId.Text + ",'" + TxtName.Text + "')", Sqlcon)
        Dim i As Integer = sqlcmd.ExecuteNonQuery()
    End Sub

<h1>How to Establish Connection string from EDMX and MVC Patteren </h1>



//Model -1
            lstemp=db.Tables();

            //Model -2
            lstemp = db.Tables.Where(x=> x.Id==2);


 List<EmployeeList> lstemp=new List<EmployeeList>();
        // GET: Employee

        Database1Entities db = new Database1Entities();

<add name="Database1Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="data source=(LocalDB)\v11.0;attachdbfilename=|DataDirectory|\Database1.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />

How to Insert Records using Edmx Concept


  [HttpGet]
        public ActionResult Create()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Create(StudentEnq empcls)
        {
            Db.StudentEnqs.Add(empcls);
            Db.SaveChanges();
            return View();
        }

Entry form View this


<div class="form-horizontal">
        <h4>StudentEnq</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.StudId, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.StudId, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.StudId, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.StudName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.StudName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.StudName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>