Tag Archives: Visual C#.Net

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.

Syntax Error INSERT INTO Statement – ADO.Net Solved


This is a common error occurred during the database operation in ADO.Net. The cause for this error is the column name matches some reserved words. To solve this issue suffix and prefix with “] and [” in column names.

Command object

If you are using command object use the following method in the insert statement.

OleDbCommand cmd = new OleDbCommand(“insert into acgroup(gname,parent,[note]) values(‘” + gname + “‘,'” + gparent + “‘,'” + gdescription + “‘” + “)”, Globals.con);
cmd.ExecuteScalar();

here ‘note’ cause the error.

Dataset and Command builder

If you are using dataset, adapter and command builder to insert records, use command builder Quickfix method to solve the issue as follows

dr = Globals.GroupDataSet.Tables[0].NewRow();
dr[“gname”] = gname;
dr[“parent”] = gparent;
dr[“note”] = gdescription;
Globals.cmdbuilder = new OleDbCommandBuilder(Globals.GroupAdapter);
Globals.cmdbuilder.QuotePrefix = “[“;
Globals.cmdbuilder.QuoteSuffix = “]”;
Globals.GroupDataSet.Tables[0].Rows.Add(dr);
Globals.GroupAdapter.InsertCommand = Globals.cmdbuilder.GetInsertCommand();
int stat = Globals.GroupAdapter.Update(Globals.GroupDataSet.Tables[0]);
if (stat > 0)
{
MessageBox.Show(“Group Information saved”);
}

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

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);

 

How to fetch data rows from a data view in C#


Data View is a representation of data table which can be used to access rows in a table. In Database programming sometimes we need to search and extract/copy rows which meet some criteria/condition. C# and .Net framework allows you to do this using rich set of objects available in Visual Studio.

In our example we have a table Groups which is used to store various accounting groups. I want to extract those groups with pgid[Primary Group ID] as 0, this is made possible with Data View object’s FindRows method.

Common.GroupTableView.Sort = "pgid";
 dv.Sort = "pgid";
 DataRowView[] nodes = Common.GroupTableView.FindRows(0);

FindRows () return a collection of data rows, so that we used array of dataRowView to store the objects.

You can use Foreach loop to get access values as follows [ add a tree view control to your C# Project]

foreach (DataRowView node in nodes)
 {
 treeView1.Nodes[0].Nodes.Add(node["name"].ToString()); 
 }
 treeView1.ExpandAll();