Tag Archives: Database

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;

        }

Insert if not exist sqllite [android]


We are femiliar with if not exits keyword when working sqllite database in Android or in web applications as well.

If not exist work with create statements, so how do we implement the same tactics in insert statement ?

Here what I found ,

Take a look at what we have

Here I got a table Entries which keep track of different entries with auto grnerated eid column. I wanna make happends only insert entries that is not exist in the table.entries.pngTodo

  1. Create a unique index on ename column
  2. usere insert or ignore into statement instead of insert into
Index the colum
create unique index Entry_name_start on Entries ( ename ) ;

This will generate a intex on column ename, which means it can’t be duplicated

The new insert insert.png
insert or ignore into "Entries" (
    id,
    ename)
values (
    :id,
    :ename)
;

It will work….

Note: I used Android Studio and plugin called DB Browser in this tutorial

Querying external database in vb6 DAO


DAO is one of many ways to access database in VB6. This tutorial tells you how you can access a .mdb database which is not pointed by the DAO Database object.

I have separate Access 2003 .mdb database files, one is pointed by the Database object conn and the other I want to access using the same connection.

SELECT * FROM [File Path][DBNAME].[TABLE]

Connection

Have a look at the connection. [May be you already know, what its look like, this for those not familiar with DAO in VB6]

Dim externalEmp As Recordset
Dim conn As Database
Set conn = OpenDatabase("D:\105443T.mdb")

Querying Records

The conn is now capable of querying any table within the 105443T database.

Say,

Set tr = conn.OpenRecordset("select * from Transactions")
If tr.RecordCount > 0 Then
 tr.MoveFirst
 tr.MoveNext
 tr.MoveFirst
 
 For c = 0 To tr.RecordCount - 1
 Debug.Print tr!date & "--" & tr!dr & "--" & tr!cr
 tr.MoveNext
 Next c
End If

Querying the external DB

Likewise, I can point external Database too with zero changes in conn object. Just specify the [path of file] [.] [data table Name] in place of the table name in the query.

Set externalEmp = conn.OpenRecordset("select * from C:\users\manoj\documents\office.emp")
If externalEmp.RecordCount > 0 Then
 externalEmp.MoveFirst
 externalEmp.MoveNext
 externalEmp.MoveFirst
 
 For c = 0 To externalEmp.RecordCount - 1
 Debug.Print externalEmp!Ename & "--" & externalEmp!edepart & "--" & externalEmp!ebasic
 externalEmp.MoveNext
 Next c
End If

The same is also possible with ADO and other connections.

That’s it.

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.

Autocomplete text to a single datagridview column in C#


We already discussed the matter how to suggest AutoComplte Text  for datagridview column in C#. This post is the extension to that post, which will explain , limit the searching capability for single column.

Task: Need Autocomplete suggest list of Text as I type for the PARTY column only[DataGridView].

datagrid

Drop the following code in the  dataGridView1_EditingControlShowing event.

TextBox auto = e.Control as TextBox;
 if (dataGridView1.Columns[dataGridView1.CurrentCell.ColumnIndex].HeaderText == "PARTY")
 {
 Common.F5GAccRegistration();
 AutoCompleteStringCollection autotxt = new AutoCompleteStringCollection();

foreach (DataRowView row in Common.AccountRegistrationTableView)
 {
 autotxt.Add(row["name"].ToString());
 }
 auto.AutoCompleteMode = AutoCompleteMode.Suggest;
 auto.AutoCompleteSource = AutoCompleteSource.CustomSource;
 auto.AutoCompleteCustomSource = autotxt;
 }
 else
 {
 auto.AutoCompleteMode = AutoCompleteMode.None;
 auto.AutoCompleteSource = AutoCompleteSource.None;
 auto.AutoCompleteCustomSource = null;

}
  • Firstly  capture the control using TextBox auto = e.Control as TextBox;
  • Secondly  populate names from the database and add to AutocompletStringCollection.
  • Thirdly  attach the collection to custom source of the control.
  • The else part simply prevent from displaying autocomplete for every columns in the datagridview.

 

 

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.

 

 

How to make database read/write in SQL


When we have the problem with READ ONLY SQL DATABASE(It can happen while we trying to attach different version database file),  first up all we need to make the security of ldf and mdf file to everyone-Full Control.( if not working,  do the same for folder and drive in which the database file resides. )
And then simply execute the script

USE [master]

Go

Alter database [readonlydb] set READ_WRITE WITH NO_WAIT

GO

Or use the query without the NO_WAIT option.

USE [master]

Go

Alter database [readonlydb] set READ_WRITE

GO