Category Archives: SQL

Return table row as string in SQL Server


You can return table rows as single row using a COALESCE trick. This can be possible by appending each row value to a variable.

The SQL Coalesce are used to handle NULL values. During the expression evaluation process the NULL values are replaced with the user-defined value.

declare @ret varchar(4000)
select @ret=coalesce(@ret+', ','') + l_name from accounts
print(@ret)

Here in my SQL script, created a variable and append all row values in the column of l_name to sql vaiable @ret and then I print the result

Even though the script can return lengthy string, it may depends on the number of rows and the capacity of varchar variable declared.

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;

        }

MSSQL : Update row from another database


This code snippet allow you to update table from another database in Microsoft SQL Server. To do this you have to use the following script.

UPDATE dbo.Address  set gstnumber= t.gst 
from olddb.dbo.address as  t  where dbo.address.id=t.id

All the data with corresponding id in the target table will be updated with the values of olddb database.

How to copy schema,data,objects,indexes, constraints from one database into another in MSSQL Server


In SQL Server you can easily copy values as well as schema/structure from one table to another using following commands

select * into db1.dbo.<destination table> from db2.dbo.<source table>

The limitation of this method is that, only copies the table schema , data. and leave the objects, indexes, triggers or constraints behind.

For example in the above command will copy columns to the destination table and leave identity column as regular, your new table will not hold any identity columns .

Solution

Solution to this problem is generate the scripts using Script Wizard and then execute the script to generate all objects and indexes

  • Right Click the database in your SQL Server Management Studio then choose Tasks- Generate scripts, choose a database then select objects type and review and wait for script.
  • Once it was generated go head choose and execute then script

All of the table structure,index,triggers,constraints will be created for your new database.

Now you are ready to copy data from one database from another using following insert command

insert into <destination table> (col1,col2 ...) 
select  col1,col2...  from fin_app_data.dbo.groups

Please leave identity column behind.

insert row if not exit -MSSQL


How to insert a row in MS SQL if the row already not inserted. There are two approach for this task

Solution 1

If Not Exists(select * from REP where NAME=? AND CODE='SALIH')
Begin
insert into REP (NAME,CODE) values ('SALIH','SALIH')
End

Solution 2

insert into REP (code,NAME)
Select 'SAFVAN1','SAFVAN' Where not exists(select * from REP where code='SAFVAN1' AND NAME='SAFVAN')

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

How to migrate MS Access database to MS SQL Server 2005 or later


Wanna migrating Access database to MS SQL Server? You have two options; programmatically deal the transaction with the new database or use Microsoft SQL  Server Migration Assistant for Access and migrate the database directly to a newly created database to SQL Server 2005 or later versions

2018-05-01_155514

Before starting with MSSQLMA Note the following

  1. Prepare your database
  2. Check your Access database version, whether it is 32 or 64 database. If it is 32 make sure that you run 32 version of MSSQLMA which can be found under Microsoft SQL  Server Migration Assistant for Access folder.
  3. There are different versions of MSSQLMA available for different versions of the Access database.

Download Migration Assistant for Windows

Use the following link to download the free Migration Assistant from Microsoft official website.

v5.3 v6.0 , v7.8

Start Migration

Use Migration Wizard to add database and convert, load and migrate functions to finish the process.

Note:

Sometimes you may need to convert your access database to 97/2000 format for easy migration otherwise it will end up with a bunch of errors.

Run appropriate versions of Migration Assistant

Delete a rows using command builder and dataset in C#


The following example simply illustrate how you can delete rows using dataset and command builder in C#. As a C# programmer you may know how important a dataset is.

Here in our example, we first create a database adapter pointing to some table rows and then build a dataset which is the local representation of database table.

Common.StockPurchase_Part = new SqlDataAdapter("select * from purchasepart where billno=" + bno, Common.con);
 Common.StockPurchasePartDataset = new DataSet();
 Common.StockPurchase_Part.Fill(Common.StockPurchasePartDataset);

Now lets create a command builder and configure the delete command of the adapter.

Common.cmdbuilder = new System.Data.SqlClient.SqlCommandBuilder(Common.StockPurchase_Part);
 Common.StockPurchase_Part.DeleteCommand = Common.cmdbuilder.GetDeleteCommand();

Now we  need to mark the rows we want to delete, using the Rows collection. In my example , I have only one rows, which is stored in the 0th position. So my code will look like

 Common.StockPurchasePartDataset.Tables[0].Rows[0].Delete();

Now all I need is just call the Update method of the adapter and it simply works.

Common.StockPurchase_Part.Update(Common.StockPurchasePartDataset.Tables[0]);

Hope you got the technique.