Menu ▼


This example shows you how to insert data from dataset or datatble to sql table using sql bulk copy in c#.

Dataset
sql bulk copy dataset

C# Code

protected void Page_Load(object sender, EventArgs e)
{
    DataSet ds = GetData();
    SqlConnection con = new SqlConnection(@"data source=.\sqlserver; user id=sa; password=123; database=Sample;");
    SqlBulkCopy bulk = new SqlBulkCopy(con);
    bulk.DestinationTableName = "tblStudents";
    foreach (DataColumn col in ds.Tables[0].Columns)
        bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
    con.Open();
    bulk.WriteToServer(ds.Tables[0]);
    con.Close();
}

DataSet GetData()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("StudentID", typeof(int));
    dt.Columns.Add("StudentName", typeof(string));
    dt.Columns.Add("RollNumber", typeof(int));
    dt.Columns.Add("TotalMarks", typeof(int));
    dt.Rows.Add(1, "Jame's", 101, 900);
    dt.Rows.Add(2, "Steave Smith", 105, 820);
    dt.Rows.Add(3, "Mark Waugh", 109, 850);
    dt.Rows.Add(4, "Steave Waugh", 110, 950);
    dt.Rows.Add(5, "Smith", 111, 910);
    dt.Rows.Add(6, "Williams", 115, 864);
    DataSet ds = new DataSet();
    ds.Tables.Add(dt);
    return ds;
}

SQL table before data inserted
SQL table before data inserted

SQL table after C# datatable/dataset data inserted using sql bulk copy
SQL table after C# datatable/dataset data inserted using sql bulk copy

Read More...
Submited By : mrkraju On 16 Dec 2015

Below example will show you how to pass null value to stored procedure parameter from c#

Passing null value for integer type parameter

//If user id is grater than 0 then assign user id other wise pass null value
if (intUserID > 0)
    SqlCmd.Parameters.AddWithValue("@UserID", intUserID);
else
    SqlCmd.Parameters.AddWithValue("@UserID", DBNull.Value);

Common Syntax

SqlCmd.Parameters.AddWithValue("@ParameterName", DBNull.Value);

Read More...
Submited By : mrkraju On 19 Nov 2015

This example shows you how to read connection string from asp.net web.config file.

Define connection string in web.config

<connectionStrings>
  <add name="myCon" connectionString="Data Source=ServerDetails;user id=sa;password=xxxxxx;database=DbName;" providerName="System.Data.SqlClient"/>
</connectionStrings>

Accessing/Reading connection string from c# code
Add reference -> using System.Configuration;

    SqlConnection con = new SqlConnection();
    con.ConnectionString = ConfigurationManager.ConnectionStrings["myCon"].ConnectionString;

Read More...
Submited By : mrkraju On 05 Nov 2015

This example shows you how to configure SQL connection string in web.config file

Windows authentication connection string

<connectionStrings>
  <add name="myCon" connectionString="Initial Catalog=DbName;Data Source=ServerDetails;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
</connectionStrings>

Sa user connection string

<connectionStrings>
  <add name="myCon" connectionString="Data Source=ServerDetails;user id=sa;password=xxxxxx;database=DbName;" providerName="System.Data.SqlClient"/>
</connectionStrings>

Read More...
Submited By : mrkraju On 04 Nov 2015
Simple Example For DataList view control in ASP.NET

<div>
<table>
<asp:DataList ID="DataList1" runat="server" OnItemCommand="DataList1_ItemCommand">
<HeaderTemplate>
<tr>
<th></th>
<th>Eno</th>
<th>Ename</th>
<th>Salary</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><asp:Button ID="btnEdit" Text="Edit" CommandName="cmdEdit" runat="server" /></td>
<td><%?#?DataBinder?.Eval(Container.DataItem,"eno") %></td>
<td><%#DataBinder.Eval(Container.DataItem,"ename") %></td>
<td><%#DataBinder.Eval(Container.DataItem,"sal") %></td>
</tr>
</ItemTemplate>
<EditItemTemplate>
<tr>
<td>
<asp:Button ID="btnUpdate" Text="Update" CommandName="cmdUpdate" runat="server" /> </td>
<td><asp:Button ID="btnCancel" Text="Cancel" CommandName="cmdCancel" runat="server" />
</td>
<td>
<asp:Label ID="lblEmpno" Text='<%#DataBinder.Eval(Container.DataItem,"eno") %>' runat="server" />
</td>
<td> <asp:TextBox ID="txtEname" Text='<%#DataBinder.Eval(Container.DataItem,"ename") %>' runat="server" /></td>
<td><asp:TextBox ID="txtEsal" Text='<%#DataBinder.Eval(Container.DataItem,"sal") %>' runat="server" /></td>
</tr>

</EditItemTemplate>
</asp:DataList>
</table>

.cs code

SqlConnection con = new SqlConnection("user id=sa;pwd=nanisqlserver;database=sample;server=.");
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack == false)
{
Filldata();
}
}

protected void Filldata()
{
SqlDataAdapter da = new SqlDataAdapter("select * from emp", con);
DataSet ds = new DataSet();
da.Fill(ds);
DataList1.DataSource = ds;
DataList1.DataBind();
}

protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName == "cmdEdit")
{
DataList1.EditItemIndex = e.Item.ItemIndex;
Filldata();
}
else if(e.CommandName=="cmdUpdate")
{
Label l = (Label)e.Item.FindControl("lblEmpno");
int empno = int.Parse(l.Text);
TextBox t1 = (TextBox)e.Item.FindControl("txtEname");
string empname = t1.Text;
TextBox t2 = (TextBox)e.Item.FindControl("txtEsal");
double empsal = double.Parse(t2.Text);
con.Open();
string query = "update emp set ename='"+empname+"', sal='"+empsal+"' where eno='"+empno+"'";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
DataList1.EditItemIndex = -1;
Filldata();
con.Close();
}
else if(e.CommandName=="cmdCancel")
{
DataList1.EditItemIndex=-1;
Filldata();
}
}
}
Read More...
Submited By : Datta On 01 Feb 2014

DataTable.Select() will filter the rows based on the filter expression.

dt.Select("State='Andhrapradesh'") will return the rows which is having state = 'Andhrapradesh'

 

Source Code:

        DataTable dt = new DataTable("ResultSet");

        dt.Columns.Add("Country");

        dt.Columns.Add("State");

        dt.Columns.Add("Place");

        dt.Rows.Add("India", "Andhrapradesh", "Charminar");

        dt.Rows.Add("India", "Andhrapradesh", "Steel Plant");

        dt.Rows.Add("India", "Maharastra", "IT Hub");

        dt.Rows.Add("India", "Tamilnadu", "Beach House");

        dt.Rows.Add("India", "Tamilnadu", "Club House");

        dt.Rows.Add("USA", "Dallas", "Airport");

        dt.Rows.Add("Australia", "Sydney", "Stadium");

        DataRow[] arydr = dt.Select("State='Andhrapradesh'");

        DataTable dtStates = dt.Select("State='Andhrapradesh'").CopyToDataTable();

 

Output of DataTable dtStates = dt.Select("State='Andhrapradesh'").CopyToDataTable();



Read More...
Submited By : mrkraju On 28 Nov 2013
By using DataTable DefaultView.ToTable() method we can get the distinct values of required columns of a datatable. the follwing example will explain how to do that.

        DataTable dt = new DataTable("ResultSet");

        dt.Columns.Add("Country");

        dt.Columns.Add("State");

        dt.Columns.Add("Place");

        dt.Rows.Add("India", "Andhrapradesh", "Charminar");

        dt.Rows.Add("India", "Andhrapradesh", "Steel Plant");

        dt.Rows.Add("India", "Maharastra", "IT Hub");

        dt.Rows.Add("India", "Tamilnadu", "Beach House");

        dt.Rows.Add("India", "Tamilnadu", "Club House");

        dt.Rows.Add("USA", "Dallas", "Airport");

        dt.Rows.Add("Australia", "Sydney", "Stadium");

        DataTable dtCountry = dt.DefaultView.ToTable("Countries", true, "Country");

        DataTable dttemp = dt.DefaultView.ToTable("Temp", true, "Country", "State");

 

In the above example dtCountry will contain the distinct Countries and dttemp will contain the distinct Countries and States.

Output of "dt"



Output of DataTable dtCountry = dt.DefaultView.ToTable("Countries"true"Country");



Output of DataTable dttemp = dt.DefaultView.ToTable("Temp"true"Country""State");


Read More...
Submited By : mrkraju On 26 Nov 2013


The overall architecture of Entity Framework as in above diagram. The Query Languages lay on top of the Object Services which relies in Entity Client to interact with the Database. The Entity Client uses the standard ADO.NET providers to physically communicate with the Database. The EDM is a cross layer that is used by Object Services and Entity Client to obtain metadata about classes.

Conceptual Model: describes about the Model classes

Mapping Model: This is where the conceptual Model is mapped to Storage Model.

Storage Model: describes about the Database schema

ADO.NET Entity Framework is an Object-Relationship Management (ORM) or Object Relational Mapping tool like Hibernate in java or N Hibernate for DOTNET. ADO.NET Entity Framework is included with .NET Framework 3.5 Service Pack 1 and Visual Studio 2008 Service Pack 1.

Microsoft clearly stated that the future of data access for the .NET platform is Entity Framework.

An ORM is a framework for persisting model objects in a relational database and retrieving them. It uses metadata information to interface with the database so it can perform both persistence and data retrieval. Here your data layer code knows nothing about the database structure as the ORM becomes a middle layer that completely hides the complexity.

The Heart of an ORM is the Mapping. The mapping technique is what bound the object and the relational worlds or Database. By mapping you specify how a class and its property are related to one or more tables in the database. This information is used by the engine of the ORM to dynamically build SQL code that retrieves data and transform them into objects. The same way, by tracking changes to objects properties; it can use mapping data to send updates back to the database. The mapping information is generally expressed as an XML file. As an alternative, some ORM uses attributes on the classes and their properties.


The Entity Framework helps the developers to focus on their application-specific business logic rather than the data access fundamentals. It provides services like change tracking, identity resolution, lazy loading, and query translation.
Read More...
Submited By : Krishna Narra On 21 Nov 2013




Search dotnetlearners.com