Tag Archives: sqlconnection

Add records to SQL DB using command builder and dataset in C#


When I talk about C# Dataset quote that it can also be used for insertion of data into the table too. In this post, we are going to learn how to.

Prerequisites

  1. Data connection with SQL
  2. Dataset
  3. Command builder object

In our example, we had MiniPurchaseInfo table and dataset associated with it.  First thing first, the dataset

SqlDataAdapter MiniPurchaseInfo = new SqlDataAdapter("select * from minipurchaseinfo", con);
 MiniPurchaseInfoDataSet = new DataSet();
 MiniPurchaseInfo.Fill(MiniPurchaseInfoDataSet, "minipurchaseinfo");

We simply make the query, filling the information to the dataset and so on. The next step is to create a row with the DataRow object and add the data row to our dataSet object.

DataRow dro;

dro = MiniPurchaseInfoDataSet.Tables[0].NewRow();
 dro["entryno"] = entryNo;
 dro["supinvoice"] = txt_invoice.Text.ToString();
 dro["supid"] = SUPID;
 dro["netamount"] = net;

add the row to the dataset.

MiniPurchaseInfoDataSet.Tables[0].Rows.Add(dro);

The final step is to set up the command build which has the capability of insertion and deletion operations with DB adapter and dataset.

System.Data.SqlClient.SqlCommandBuildercmdbuilder = new System.Data.SqlClient.SqlCommandBuilder(MiniPurchaseInfo);

Set insert command of the adapter from the command builder object

MiniPurchaseInfo.InsertCommand = cmdbuilder.GetInsertCommand();

Call the update command of the adapter which will update the new row stored in the dataset into the database table.

int r = MiniPurchaseInfo.Update(MiniPurchaseInfoDataSet.Tables[0]);

The update will return a none zero value when the insertion performed.

How to create a SQL dataset in C#


The dataset in Visual Studio.Net represents a localized version of data from the database you are using. You can use the data stored in the dataset, no matter your connection is closed or not. 

Let’s see how a dataset can be created. First, up all you need SQL connection, see how to setup connection and adapter.

Build up your query with appropriate columns, then use Fill method of adapter class to fill your dataset as follows.

SqlDataAdapter  ProductTableAdapter = new SqlDataAdapter(“select * from productMaster”, con);

DataSet  ProductDataset=new DataSet()
ProductTableAdapter.Fill(MDataset, “ProductMaster”);

* Here ‘con’ is the SQL connection object.

Now you are ready to use ProductDataset

Ado.Net connection,adapter,dataset and dataview explained


I wrote this post for those who have no idea about C# database connection.

When starting with Visual Studio language like C#/VB.Net, you need to understand the concept of adapter, dataset and data view.

SQL connection

SQL connection class helps you to build the connection string.Visual studio can build connection string for you or  can create your in App config [Solution Explorer]

<add name=”MACCon” connectionString=”Data Source=(LocalDB)\MSSQLlocalDB;AttachDbFilename=e:\Developerm\c# Projects\MAcc_Prime\MAcc\MACCDATA.mdf;Integrated Security=True” />

Use SQL connection object to configure the connection as follows

con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[“MACCon”].ConnectionString;
con.Open();

Now you’re ready to use database

Data Adapter

The data adapter is the bridge between database tables with the application. The Adapter class helps you to the configugure table. The Adapter connects to the database throw the connection object.

geTradp = new SqlDataAdapter(“select dr,cr from accounttransactions where entry=” + mcode + ” and acid=” + acid + ” and tdate>='” + sdate + “‘ and tdate<='” + edate + “‘”, Common.con);

Here “conn” is the SQL database connection object

Dataset and Data Views

DataSet is the local version of your database table, you can work with the dataset and its data even if the connection has closed. Data Set accompanying with command builder also simplifies operation like adding new data, updating /deleting data etc

Dataset can also be used as data source for controls like data grid view.

Data View is a special object which offers table view so that you can fetch columns, rows, filter, extract rows with Find, FindRow methods loops through recordsets with the help subclasses of data view like Data RowView.

Let’s begin with .Net programming