BULK DATA INSERT FROM CSV TO SQL SERVER

BULK INSERT TableBulkTest
FROM 'D:\BulkTest.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Insert data from One Table To another in SQL SERVER

insert into NEWTABLENAME (name,fathername) select name,fname from OLDTABLENAME


This Query user to copy data from one table to another, we can also use WHERE clause witth SELECT statement to verify data

Such as: 

insert into NEWTABLENAME (name,fathername) select name,fname from OLDTABLENAME
Where name IS NOT NULL

Add ScrollBar in CheckBox List in ASP.Net

It is not possible in Asp.Net but we can use by HTML TAG DIV

<divstyle="overflow-y:scroll; width:600px; height:500px">
</div>

How To Use Split in Asp.net

 string[] arr=Txtname.Text.Split(new char[] {','});

how to delete file from folder in asp.net by FTP

 SqlDataAdapter da = new SqlDataAdapter("Select Path from Table_Image where loginID='" + TxtNo.Text + "'", connection);
        DataSet ds = new DataSet();
        da.Fill(ds);
        if (ds.Tables[0].Rows.Count > 0)
        {
         

            string host = "ftp://"; (Folder Path  Where image save)
            string userName = "uname";            (FTP User Name)
            string pwd = "password";             (FTP Password)
            string fileName = ds.Tables[0].Rows[0]["Path"].ToString();
            fileName = fileName.Replace("url", "");
            string remoteFile = Path.GetFileName(fileName);
            string FTPFilePath = Path.Combine(host, remoteFile);


            FtpWebRequest reqFTP = (FtpWebRequest)WebRequest.Create(FTPFilePath);
            reqFTP.Credentials = new NetworkCredential(userName, pwd);
            reqFTP.KeepAlive = false;
            reqFTP.UseBinary = true;
            reqFTP.UsePassive = false;
       
            reqFTP.Method = WebRequestMethods.Ftp.DeleteFile;

            FtpWebResponse resFTP =(FtpWebResponse)reqFTP.GetResponse();
           string desc=resFTP.StatusDescription;
}

Remove Duplicate Rows from DataBase in Sql Server

SELECT * FROM Tab_Test WHERE serial_num NOT IN (SELECT MIN(serial_num) FROM dbo.Tab_Test GROUP BY col1,col2)

serial_num is an auto indentity column

DELETE FROM Tab_Test WHERE serial_num NOT IN (SELECT MIN(serial_num) FROM dbo.Tab_Test GROUP BY col1,col2)

Upload Data from CSV file to Sql Server

1.FileUploader1 is a fileUploadControl

2.name,fathername,address are the columns which same as csv file name and sql server database 

3.G:\\FileName is your local computer path where CSV file save.

           

FileUploader1.SaveAs("G:\\FileName\\" + FileUploader1.FileName);

                // You can get connected to driver either by using DSN or connection string

                // Create a connection string as below, if you want to use DSN less connection.

The DBQ attribute sets the path of directory which contains CSV files

                string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + "G:\\FileName\\" + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";

                string Query;

                DataSet ds = new DataSet();

                System.Data.Odbc.OdbcConnection conn;

                //Create connection to CSV file

                conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim());

                // For creating a connection using DSN, use following line

                //Open the connection

                conn.Open();

                //Fetch records from CSV

                Query = "select * from [" + FileUploader1.FileName.ToString() + "]";

                System.Data.Odbc.OdbcDataAdapter oledaObj = new System.Data.Odbc.OdbcDataAdapter(Query, conn);

                //Fill dataset with the records from CSV file

                oledaObj.Fill(ds);

                int j = 0;

                if (ds.Tables[0].Rows.Count > 0)

                {

                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

                    {

                        try

                        {

                            DataSet ds2 = new DataSet();

                            SqlDataAdapter adap = new SqlDataAdapter("INSERT INTO TableDetails (name,fathername,address)  VALUES('" + ds.Tables[0].Rows[i]["name"].ToString() + "','" + ds.Tables[0].Rows[i]["fathername"].ToString() + "','" + ds.Tables[0].Rows[i]["address"].ToString() + "')", ConfigurationManager.ConnectionStrings["Conn"].ConnectionString.ToString());

                            adap.Fill(ds);

                            j++;

                        }

                        catch (Exception ex)

                        {

                        }

                    }

                    LblMsg.Text = "File Uploaded Successfully, No. of record save - " + System.Convert.ToString(j);

                    LblMsg.Visible = true;

                }

                conn.Close();