Category Archives: ado.net

List all SQL Instances on a local machine in C#


This is just another SQL C# articles which let you learn how to make a list of names of SQL instance available on your Microsoft SQL Server using C# code.

This is made possible using Registry entries.

     private IEnumerable<string> ListLocalSqlInstances(RegistryKey hive)
        {
            const string keyName = @"Software\Microsoft\Microsoft SQL Server";
            const string valueName = "InstalledInstances";
            const string defaultName = "MSSQLSERVER";

            using (var key = hive.OpenSubKey(keyName, false))
            {
                if (key == null) return Enumerable.Empty<string>();

                var value = key.GetValue(valueName) as string[];
                if (value == null) return Enumerable.Empty<string>();

                for (int index = 0; index < value.Length; index++)
                {
                    if (string.Equals(value[index], defaultName, StringComparison.OrdinalIgnoreCase))
                    {
                        value[index] = ".";
                    }
                    else
                    {
                        value[index] = @".\" + value[index];
                    }
                }

                return value;
            }
        }


 public IEnumerable<string> ListLocalSqlInstances()
        {
            if (Environment.Is64BitOperatingSystem)
            {
                using (var hive = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry64))
                {
                    foreach (string item in ListLocalSqlInstances(hive))
                    {
                        yield return item;
                    }
                }

                using (var hive = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry32))
                {
                    foreach (string item in ListLocalSqlInstances(hive))
                    {
                        yield return item;
                    }
                }
            }
            else
            {
                foreach (string item in ListLocalSqlInstances(Registry.LocalMachine))
                {
                    yield return item;
                }
            }
        }

How to use the function

The function has two part, one with argument which dig for registry element another will extract the information.

 txt_instance.ItemsSource = SqlHelper.ListLocalSqlInstances();

The a above line will serve the list of string ( instance names ) and serve as itemsource for a combo box control.

How to Check for a database on SQL Server using C#


This is just another SQL C# articles which let you learn how to check for a database existence on SQL Server.

All you have to do query names in sys.databases table and make sure it is available to use. You can create a function for the purpose.

public static bool IsDBExist(string server, string db)
        {
            List<string> list = new List<string>();

            // Open connection to the database
            string conString = null; ;
            
                conString = "server=" + server + ";uid=sa;";
           
            bool has = false;
            try
            {
                using (SqlConnection con = new SqlConnection(conString))
                {
                    if (con.State == ConnectionState.Open) con.Close();
                    con.Open();


                    // Set up a command with the given query and associate
                    // this with the current connection.
                    using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases where name='" + db + "'", con))
                    {
                        using (IDataReader dr = cmd.ExecuteReader())
                        {

                            while (dr.Read())
                            {
                                list.Add(dr[0].ToString());
                            }
                            if (list.Count > 0) has = true;
                        }
                    }
                    con.Close();
                }
            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message.ToString());
            }
            return has;

        }

DataGridView meets Linq


DataGridView is a tool for displaying, modifying, entering values in Visual Studio.Net. Usually we are using loops or readers to traverse through rows or columns.

Linq query make it possible to do these take with few lines, instead of complicated loops.

We have a data entry GridView which accepts purchase details such as name,qty,price etc. Lets get the sum of quantity supplied in the grid using Linq query

We do the following

  1. Create List of values
  2. Call the Sum() for total function for the column we specified

The Linq Querry

var qt = from DataGridViewRow row in dataGridView1.Rows where row.Cells[COL_QTY.Index].FormattedValue.ToString().Trim().Length!=0 && row.Cells[COL_QTY.Index].FormattedValue != null select ( row.Cells[COL_QTY.Index].FormattedValue.ToString().Trim());

We have to make sure the cell is not null or it has any values at all, we place where section, otherwise the System will fire you for an unhandled exception, lol.

Summing up values

txt_tqty.Text = qt.Sum(c => Convert.ToDouble(c)).ToString();

That’s all

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

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

 

 

 

How to use dataView rowfilter for searching rows in C#.Net


You can simply use SQL query statements to retrieve desired data from database server with C# and ADO.Net. But there are plenty of ways to search your localized version of data which is stored in dataSet and dataView.

We already learn how to use Find and Findrow methods for searching data in Visual Studio App. This post will show how to use row filter property of  DataView for sorting data rows.

  1. First up all you need to create Data adaptor, dataSet, and dataView.
  2. In our example, I have a date field in the database table, so that I can filter for specific date ranges as follows
 Common.AccountReportDV.RowFilter = "invoicedate>='" + dateTimePicker1.Value.ToShortDateString() + "' and invoicedate<='" + dateTimePicker2.Value.ToShortDateString() + "'";

Here common is a public static class where kept the Database functions. The above statement will be looking for data between the two dates.

You have to use Common.AccountReportDV and not Common.AccountReportDV.Table.rows  for retrieving the result as follows.

foreach (DataRowView row in Common.AccountReportDV)
 { 

console.WriteLine(row["<column name here>"]);

 }

DataRowView is a special class which helps to access data rows in a DataView object.

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.

Create custom form controls in C#.Net


.Net frame provide rich set of control to design  application GUI with a drag and drop actions. Sometime we love to have extend these control features. This can be achieved using Windows Forms Control Library Projects. Let’s learn how to do.

Extended TextBox control

Our extended control have following features
  • Custom got focus color
  • Custom Lost focus color
  • New Methods
Using the new Text Box you can enable different Leave/Enter colors. The example look like simple but it will demonstrate how you can add new properties for using at run time and design time.

Quick start

  1. Add New  Windows Forms Control Library project in Visual Studio .Net 2012/15.
  2. Drag a Text Box to the user control
  3. Rename the User control as “CPTextBox” where CP stands for Code Poet.
    Windows Forms Control Library Project
Now we need a Windows Application to test our CPTextBox, add a new project by,
  1. Go to File – Add – New Project – Windows Form Application (name it as testProject)
  2. Go to Solution Explorer – There is your two project, right click the Windows Application Project, and choose Set as Start Up Project.

We are almost ready to make changes to our custom control. Double click our custom control  and the following methods and drop properties to our code.

namespace CodePoetControls
{

public partial class CPTextBox: UserControl
{
Color Ecolor;
Color Lcolor;

public CPTextBox()
{
InitializeComponent();
textBox1.BackColor = Color.White;

}

private void textBox1_Enter(object sender, EventArgs e)
{
textBox1.BackColor = Ecolor;
}
private void textBox1_Leave(object sender, EventArgs e)
{
textBox1.BackColor = Lcolor;
}

public string GetText()
{
return (textBox1.Text);
}

[Browsable(true)]
[Category(“Extented Properties”)]
[Description(“Get input Text”)]
[DisplayName(“BoxText”)]
public string BoxText
{
set
{

textBox1.Text = value.ToUpper().Trim();

}
get
{
return (textBox1.Text  );
}
}
[Browsable(true) ]
[Category(“Extented Properties”)]
[Description (“Set Focus Color”)]
[DisplayName(“Enter Color”)]
public Color  EnterColor
{
set
{
Ecolor  = value ;
base.OnEnter(new EventArgs() );
}
get
{
return (Ecolor );

}
}

[Browsable(true)]
[Category(“Extented Properties”)]
[Description(“Set Lost Focus Color”)]
[DisplayName(“Leave Color”)]
public Color LeaveColor
{
set
{
Lcolor  = value;

}
get
{
return (Lcolor );
}
}

}
}

Compiling the User Control project.

After you successfully add the necessary code portion and ready to make your control usable. Right click the Custom control project and Build. It will generate necessary .dll files for your project.

Testing the Control

For using the control , go to the second project, open the Form. The Tool Box will show your new Control, just drag and drop CPTextBox.

The Properties Windows Categorized view will showcased your new Controls Extended Properties and try to change the Leave Color and Enter Color respectively.

If you wish to have more changes to the control, go to the custom control project and make necessary changes and Rebuild.

Distributing and using the control

Distributing and using the Dll file. For working with the new control you only need the compiled Dll not the custom project. You can add it by

  • Right click Tool Box – Choose Items
  • Brow the Dll of your custom control
  • Enable the control and it will be available on your control box.

Note:

The following code snippet will make your properties available at design time and can be accessible by using Properties.
        [Browsable(true)]
        [Category(“Extented Properties”)]
        [Description(“Set Lost Focus Color”)]
        [DisplayName(“Leave Color”)]

Using the existing Property name as Display Name will hide the default, for example if you use Text as Display Name for your property and the default property will be discarded and will be unavailable.

That’s all I have today
 

How to get access to all column names of table in VB.Net/ADO.net


Using ‘getSchema’ method we can access database properties such as Column names, Tables name etc. ,with some tricky code in Visual Basic.Net . Here is how you can access all columns names in a Access table with OLEDB connection in VB ADO.net 2013.

Sub AddListViewColumsAuto( tbl As String)
Dim Company As OleDb.OleDbConnection
 Company = New OleDb.OleDbConnection
 Company.ConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\.Net  Project\Accounting Pack\AccountingPack.accdb”
 Company.Open()
 Dim cmd As New OleDb.OleDbCommand(“Select * from ” & tbl, Company)
 Dim columns As String = “Columns”
 Dim dt As DataTable = Company.GetSchema(columns)
For Each row As DataRow In dt.Rows
 console.writeline(row.Item(“COLUMN_NAME”).ToString)
Next
Company.Close()
End Sub

I

First we create connection object, Company and a connection string. Then we need a OleDb.OleDbCommand object to set database to point a particular table, so that we created the cmd object and used it for query the input table.

II

In the next stage we created a column string in order to point Columns in the table. Using Getschema() method of connection object Company we stored each and every column name in the connection to the table variable dt.

III

In the final step we can access all rows from the dt which contain column names from table dt using COLUMN_NAME  field.

Here is the video tutorial of how you can access and add column names to the console.