Tag: ado.net

Insert rows to DatagridView in C#.Net/.Net


DataGridView is an easiest option for reports as well as other data operations in C#.Net. In this post I will show how to use methods to add rows to a datagrid from controls like textbox.

We can add new rows to a datagridview in three different ways

  • Using a DataTable
  • Using Add Row method
  • Using Set Value method
With a Data Table

Create a data Table and Row and add it to the Grid using Add method of Rows.

         DataTable dt = new DataTable();
         DataRow dr;
         dataGridView1.DataSource = dt;
         dr = dt.NewRow();
         dt.Columns.Add("Column1");
         dt.Columns.Add("Column2");
         dr["column1"] = "Value1";
         dr["column2"] = "Value2";
         dt.Rows.Add(dr);
With param arguments

The Add method of row can be used to insert rows with array of objects as follows

dataGridView2.Rows.Add("Value1", "Values2");

With Set Value

Same as the above we can also use the SetValue method too

        int rid;
        try
        {
            rid = dataGridView2.Rows.Count;
            dataGridView2.Rows[rid].SetValues("value1", "Value2");
        }
        catch (ArgumentOutOfRangeException exc)
        {
            rid = dataGridView2.Rows.Add();
            dataGridView2.Rows[rid].SetValues("value1", "Value2");
        }

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

 

How to delete rows using simple sql statement in C#


As we have seen how to delete rows in a dataset  in C#.Net, there are simple ways to do the same using SQL command object. All we need is an sql statement and an Object to do the job.

con.open();Common.cmd = new SqlCommand("delete from purchasepart where billno=" + b1, Common.con);

Common.cmd.ExecuteNonQuery();con.close();

cmd object configure the delete statement and call the ExecuteNonQuery() , and will delete those rows you are specified. Keep in mind that the connection should kept open while the ExecuteNonQuery(); method invoked.

Inserting Data rows with Parameters and Stored procedure in C# and MS SQL Server


In C# you can do your SQL things in many ways, one of the safest and fastest method in execute SQL commands from SQL server itself. Suppose you want to new row to Product table,

  1. First you need to setup SqlDataAdapter
  2. Create Parameter Objects
  3. Create SQL Procedure with Parameters
Lets do one by one

SqlDataAdapter

con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings
["MACCon"].ConnectionString;
 con.Open();
 ProductTableAdapter = new SqlDataAdapter("select * from productMaster", con);
We are now accessed the Product table with SqlDataAdapter and ready to create Parameter sets.

Setting up Parameters with values

ProductTableAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
 ProductTableAdapter.SelectCommand.CommandText = "CRUDS_PRODUCTMASTER";
 ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@Item", cmb_item.Text.ToString().ToUpper()));
 ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@mfr", cmb_mfr.Text.ToString().ToUpper())); Common.ProductTableAdapter.SelectCommand.ExecuteScalar();
with the SqlParameter  we can pass values to the SQL Procedure that we are going to create in next step. The commandType of stored procedure indicate the it is StoredProcedure. Now all we need is a Procedure.

Design your  SQL Stored Procedure

Open you SQL Server Explorer or SQL Server Itself and expand Procedure node and drop the following line of code.
ALTER PROCEDURE dbo.CRUDS_PRODUCTMASTER
 (
 @item nchar(10),
 @mfr nchar(10),
 )
 AS
 Begin
 insert into productmaster  (item,mfr) values(@item,@mfr,);
 End
The parameters in C# and SQL should match otherwise it will cause errors.

VB.Net tutorial: How to add all columns to of table into Listview in VB.Net


How to add all column names into a listview Column header in VB.Net, here is my complete tutorial for you