Wednesday 11 December 2013

C# - divide a long database (e.g. SQL) query into peices.

Description: 
recently i was having problems while passing a long query and executing it. it was getting really confusing so, i divided into pieces so that it could be easy to understand. Code is given below:

Data Table Design and Script:


Script:
USE [Temp]
GO

/****** Object:  Table [dbo].[Table_1]    Script Date: 12/11/2013 22:46:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_1](
[DIST_NAME] [nvarchar](50) NULL,
[CREATED_BY] [nvarchar](50) NULL,
[led_code] [smallint] NULL,
[LED_ADDRESS] [nvarchar](50) NULL,
[LED_CITY] [nvarchar](50) NULL,
[LED_STATE] [nvarchar](50) NULL
) ON [PRIMARY]

GO

Code:
       
        string DIST_NAME = "dist", CREATED_BY = "created", led_code = "1", LED_ADDRESS = "led add,re,.234u)&&(^*%*%", LED_CITY = "N delhi", LED_STATE ="delhi";

        private void Form1_Load(object sender, EventArgs e)
        {
            /// change the connection string according to you
            string conne=@"Data Source=HTCS-PC\SQLEXPRESS;Initial Catalog=Temp;Integrated Security=True";
            SqlConnection scon =new SqlConnection (conne);

            string comand =""/// copy this query as it is

            comand += "insert into Table_1 (DIST_NAME,CREATED_BY, led_code,LED_ADDRESS,LED_CITY,LED_STATE) values (";
            comand +="'"+DIST_NAME+"'";
            comand +=",'" + CREATED_BY + "'";
            comand += "," + led_code;
            comand +=",'"+LED_ADDRESS+"'";
            comand +=",'"+LED_CITY+"'";
            comand +=",'"+LED_STATE+"')";


            SqlCommand scom = new SqlCommand(comand, scon);

            scon.Open();

            scom.ExecuteNonQuery();

            scon.Close();
        }