Category Archives: Visual Studio 2016/17

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 change the Back color of MDI Form – C#


As a C# programmer, you may have used Back Color property for changing the color of the background for control as well as Windows form. You can also found that this not works with MDI Parent form.

MDI is the container for child forms or control, actually, it’s inherited from the panel [leave it no…lol].

Ok, all you want to repaint all the controls in it, even though there are no controls were placed, think about the inherited panel. All we have to do is try to cast every control as MDIClient and loop through it and change the color as follows

MdiClient ctlMDI;

// Loop through all of the form's controls looking
 // for the control of type MdiClient.
 foreach (Control ctl in this.Controls)
 {
 try
 {
 // Attempt to cast the control to type MdiClient.
 ctlMDI = (MdiClient)ctl;
 // Set the BackColor of the MdiClient control.
 ctlMDI.BackColor = Color.Gold;
 }
 catch (InvalidCastException exc)
 {
 // Catch and ignore the error if casting failed.
 }
 }

Place the code on Load Event of MDI Parent Form

How to Split strings using separator symbols in C#


We had some interesting string as follows which need to split and take the desired portion. C#.Net provides special string function called Split, which helps you broke complex strings into fragments.

Here are our Stings look like

121 | Whole Sale Rate

131 | Retail Rate

We begin with initialization of string and then build separator array, then call the split method. I guess you are familiar with the string array if you don’t just look at the 3rd line.

string  st=”121 | Whole Sale Rate”;

Here  “|” is the separator

string[] separator= { “|” };

string []selRateStr = null;

Let’s split the string with Split, which is the function available with string object also with ToString() too.

selRateStr =st.Split(separator,StringSplitOptions.None);

the first element in the selRateStr will be 121, which can be accessed as selRateStr [0];

Here is the authentic link from MSDN which may help you to learn Split function in detail.

 

 

How to create a DataView in C#


DataView is special table view which allows you to traverse through rows and columns respectively. C# DataView is an offline data holder as DataSet.

What you need is

  1. A Database connection object
  2. A Data Adapter which configures the data table using SQL query.
  3. And a dataset which holds the table information.

Create a data view object and initialize it with the dataset tables name as follows

SqlDataAdapter AccountRegistration = new SqlDataAdapter(“select * from accountregistration order by grpid”, Common.con);

DataSet AccountRegistrationDataset=new DataSet();
AccountRegistration.Fill(AccountRegistrationDataset);
DataView AccountRegistrationTableView = new DataView(AccountRegistrationDataset.Tables[0]);

Learn about more DatView operations

 

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