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.

Extract Rows from DataTable using AsEnumerable and Linq Querry

As a database programmer you may love the for..loop, but as .Net programmer you can use many advanced features like Enumerables,Linq etc, which provide ease and peace.

Let’s begin with a DataSet, have many rows.My Products table contain a list of Medicine names. All I want to get the whole data in two line code with the Help of Linq query.

Language-Integrated Query (LINQ) is the name for a set of technologies based on the integration of query capabilities directly into the C# language Know More

Adapted from Doc.Microsft.com

Use the AsEnumerable()

AsENumerable method allows you to fetch rows as Enumerable string array, which actually not an array at all. We her try to fetch the rows an variant type variables with a Linq query.

Linq Query
var enum_names =from products in Product_dataset.Tables[0].AsEnumerable() select 
products.Field<string>("name");

Print Result

 foreach (string str in enum_names){
   Console.WriteLine(str);
}    

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

 

 

 

Add column names to listview using dataSet in C#

When we need to populate various reports, usually we also need to supply customized column names too. We can easily grab column name from the database using objects like DataSet in C#

In our example, we use SQL database objects as follows.

AccountReportAdapter = new SqlDataAdapter(sql, con);
AccountReportDS = new DataSet();
AccountReportAdapter.Fill(AccountReportDS);

Get started with a foreach statement and store the names into a List collection object.

List<string> hds = new List<string>();

hds.Clear();
foreach (DataColumn c in Common.AccountReportDS.Tables[0].Columns)
{
hds.Add(c.ColumnName.ToString());
}

The hds list simply store the column name, just like an array does. Later we can use the list for supplying heads for ListView  as follows

public void ReportHeader(List<string> ar)
{
ColumnHeader headers = new ColumnHeader();
listView1.View = View.Details;
headers.Text = ” “;
headers.Width = 0;
listView1.Columns.Add(headers);
foreach (var htext in ar)
{
headers = new ColumnHeader
{
Text = htext.ToString(),
Width = htext.ToString().Length * 20
};
listView1.Columns.Add(headers);
}
}

if (hds != null) ReportHeader(hds);

 

Delete a rows using command builder and dataset in C#

The following example simply illustrate how you can delete rows using dataset and command builder in C#. As a C# programmer you may know how important a dataset is.

Here in our example, we first create a database adapter pointing to some table rows and then build a dataset which is the local representation of database table.

Common.StockPurchase_Part = new SqlDataAdapter("select * from purchasepart where billno=" + bno, Common.con);
 Common.StockPurchasePartDataset = new DataSet();
 Common.StockPurchase_Part.Fill(Common.StockPurchasePartDataset);

Now lets create a command builder and configure the delete command of the adapter.

Common.cmdbuilder = new System.Data.SqlClient.SqlCommandBuilder(Common.StockPurchase_Part);
 Common.StockPurchase_Part.DeleteCommand = Common.cmdbuilder.GetDeleteCommand();

Now we  need to mark the rows we want to delete, using the Rows collection. In my example , I have only one rows, which is stored in the 0th position. So my code will look like

 Common.StockPurchasePartDataset.Tables[0].Rows[0].Delete();

Now all I need is just call the Update method of the adapter and it simply works.

Common.StockPurchase_Part.Update(Common.StockPurchasePartDataset.Tables[0]);

Hope you got the technique.

Find rows with Find() in c#

Database programming is easy with Visual Studio.Net projects, it offers a variety of methods to visualize the programming logic. Here in our example, we demonstrate how you can extract specific rows from a TableView, which is the local representation of the SQL Table/Database Table View.

First I need a table view, like

 ProductTableAdapter = new SqlDataAdapter("select * from productMaster", con);
 ProductTableAdapter.Fill(ProductTableDataset, "ProductMaster");
 ProductTableView = new DataView(ProductTableDataset.Tables[0]);

Now I am going to find some rows for a specific product in the table view using the Find() method.

taekproduct="Citrizine Tab";

Common.ProductTableView.Sort="product";
int i = Common.ProductTableView.Find(takeproduct);

First I need to specify the Sort column which is the same column with the value I want to search. Second the product itself.

The Find() returns the row number instead of the row itself which can be used to access the row you want. If it’s 1 you can make sure you succeeded to find the row with takeproduct value otherwise the value will be -1.

Then I extract the tax rate from the table.

 if (i != -1)
 { takecgst = takesgst = takeigst = 0;
 double.TryParse( Common.ProductTableView[i]["cgst"].ToString(),out cgstrate);
 double.TryParse(Common.ProductTableView[i]["sgst"].ToString(), out sgstrate);
 double.TryParse(Common.ProductTableView[i]["igst"].ToString(), out igstrate);}

 

that’s all I got today.