How to take SQL database back via SQL Script

BACKUP DATABASE [MyDatabaseName]
TO DISK = N'C:\MyDatabaseName.bak'
GO
BACKUP LOG [MyDatabaseName]
TO DISK = N'C:\MyDatabaseName.bak'
GO

IE8 not opening popups using javascript

This is because spaces/dashes in popup name in java script code. Dont input space/dash in popup name

window.OpenPopUp('AddRequest.aspx', 'AddRequest', '350px', '400px'); 

HOW TO RUN C# WINDOWS SERVICE IN 32-BIT MODE IN 64-BIT OPERATING SYSTEM

1. Open Command prompt as 'Administrator'
2. move to the path cd C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\CorFlags.exe
3. then type the command CorFlags.exe /32BIT+ c:\yourpath\yourservice.exe
4. above command force your windows service to run in 32-bit mode
5. to stop running in 32-bit, run below command
6. CorFlags.exe /32BIT- c:\yourpath\yourservice.exe

Note: Before running point 3 or 6, make sure that your windows service is stopped. CorFlags.exe may not be available in all Operating Systems. Copy/download this exe file from available location and place it anywhere in the server (effects to point2)

For more references, 

https://sites.google.com/site/kishorenetblog/asp-net/corflags-exe

Yours

Venkat

HOW TO CONVERT EXCEL FILE INTO CSV FILE USING C#.NET

static void convertExcelToCSV(string sourceFile, string worksheetName, string targetFile)

        {

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile + ";Extended Properties=\" Excel.0;HDR=Yes;IMEX=1\""; 

            OleDbConnection conn = null;

            StreamWriter wrtr = null;

            OleDbCommand cmd = null;

            OleDbDataAdapter da = null; 

            try

            {

                conn = new OleDbConnection(strConn);

                conn.Open();

                cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);

                cmd.CommandType = CommandType.Text;

                wrtr = new StreamWriter(targetFile);

                da = new OleDbDataAdapter(cmd);

                DataTable dt = new DataTable();

                da.Fill(dt);

                for (int x = 0; x < dt.Rows.Count; x++)

                {

                    string rowString = "";

                    for (int y = 0; y < dt.Columns.Count; y++)

                    {

                        rowString += "\"" + dt.Rows[x][y].ToString() + "\",";

                    }

                    wrtr.WriteLine(rowString);

                }

                Console.WriteLine();

                Console.WriteLine("Done! Your " + sourceFile + " has been converted into " + targetFile + ".");

                Console.WriteLine();

            }

            catch (Exception exc)

            {

                Console.WriteLine(exc.ToString());

                Console.ReadLine();

            }

            finally

            {

                if (conn.State == ConnectionState.Open)

                conn.Close();

                conn.Dispose();

                cmd.Dispose();

                da.Dispose();

                wrtr.Close();

                wrtr.Dispose();

            }

        }

SQL DATE FORMATS

How to bind image url in gridview

in .ASPX page

<asp:GridView ID="grd1" runat="server" Width="100%" AutoGenerateColumns="false">

        <Columns>

        <asp:TemplateField>

        <ItemTemplate>

        <asp:Image runat="server" ID="img1" ImageUrl='<%# Eval("product_image","~/Images/{0}") %>' Height="30px" Width="30px"/>

        </ItemTemplate>

        </asp:TemplateField>

        <asp:BoundField DataField="prod_code" HeaderText="Code" />

        <asp:BoundField DataField="prod_name" HeaderText="Name" />

        <asp:BoundField DataField="prod_price" HeaderText="Price" />

        <asp:BoundField DataField="prod_add_date" HeaderText="Date" />

        </Columns>

        </asp:GridView>

In .aspx.CS

private SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn1"].ToString());

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                DataTable dt = new DataTable();

                SqlDataAdapter da = new SqlDataAdapter(@"select * from products", cn);

                da.Fill(dt);

                if (dt != null)

                {

                    grd1.DataSource = dt;

                    grd1.DataBind();

                }

            }

        }

        protected void btnSubmit_Click(object sender, EventArgs e)

        {

            try

            {                

                FileInfo image_file = new FileInfo(fu1.FileName);

                string des_path = HttpContext.Current.Server.MapPath("") + "\\Images";

                if (!Directory.Exists(des_path))

                    Directory.CreateDirectory(des_path);

                string file_extension = image_file.Extension;

                des_path += "\\" + txtCode.Text.Trim() + file_extension;

                fu1.SaveAs(des_path);

                SqlCommand cmd = new SqlCommand(@"INSERT INTO [dbo].[products]

           ([prod_code]

           ,[prod_name]

           ,[prod_price]

           ,[product_image]

           ,[prod_add_date])

     VALUES

           ('" + txtCode.Text.Trim() + "','" + txtName.Text.Trim() + "','" + txtPrice.Text.Trim() + "','" + txtCode.Text + file_extension + "',GETDATE())", cn);

                cn.Open();

                cmd.ExecuteNonQuery();

                cn.Close();

            }

            catch (Exception ex)

            {

                Response.Write(ex.Message.ToString());

            }

        }

RUN SQL SCRIPTS USING BATCH FILE IN C#.NET

//This method creates a batch file in given location for given db credentials. Here script1.sql is sql script file.
 private void createbatch(string filepath,string filename,string dbname,string dbserver,string dbuser,string dbpwd)
        {
            string scriptfile1="\"script1.sql\"";
            string path = filepath + "\\" + filename;
            string text = @"@echo off";
            text += "\r\n";
            text += "\r\n";
            text += @"REM  **** start: database configuration details where you would like to create views ***** ";
            text += "\r\n";
            text += @"SET PMVIEWS_DATABASE=" + dbname;
            text += "\r\n";
            text += @"SET PMVIEWS_DB_SERVER=" + dbserver;
            text += "\r\n";
            text += @"SET PMVIEWS_DB_USER=" + dbuser;
            text += "\r\n";

            text += @"SET PMVIEWS_DB_PWD=" + dbpwd;
            text += "\r\n";
            text += @"REM  **** end: database configuration details where you would like to create views ***** ";
            text += "\r\n";
            text += "\r\n";
            text += @"Sqlcmd -S %PMVIEWS_DB_SERVER% -d %PMVIEWS_DATABASE%  -U %PMVIEWS_DB_USER% -P %PMVIEWS_DB_PWD% -i " + scriptfile1 + "";
            text += "\r\n";
            using (StreamWriter strwr = File.AppendText(path))
            {
                strwr.WriteLine(text);
                strwr.Flush();
                strwr.Close();
            }
        }

//This method runs given batch file. Make sure that this batch file and sql script file contains in same folder.
        private void ExecuteBatch(string strFilePath)
        {
            string fullPath = Directory.GetParent(strFilePath).FullName;
            // Create the ProcessInfo object
            System.Diagnostics.ProcessStartInfo psi = new System.Diagnostics.ProcessStartInfo("cmd.exe");
            psi.UseShellExecute = false;
            psi.RedirectStandardOutput = true;
            psi.RedirectStandardInput = true;
            psi.RedirectStandardError = true;
            psi.WorkingDirectory = fullPath;// strPath;

            // Start the process
            System.Diagnostics.Process proc = System.Diagnostics.Process.Start(psi);
            // Open the batch file for reading
            System.IO.StreamReader strm = System.IO.File.OpenText(strFilePath);
            // Attach the output for reading
            System.IO.StreamReader sOut = proc.StandardOutput;
            // Attach the in for writing
            System.IO.StreamWriter sIn = proc.StandardInput;
            // Write each line of the batch file to standard input
            while (strm.Peek() != -1)
            {
                sIn.WriteLine(strm.ReadLine());
            }
            strm.Close();
            // Exit CMD.EXE
            string stEchoFmt = "# {0} run successfully. Exiting";
            sIn.WriteLine(String.Format(stEchoFmt, strFilePath));
            sIn.WriteLine("EXIT");
            // Close the process
            proc.Close();
            //this.AfterInstall += new InstallEventHandler(ServiceInstaller_AfterInstall);
            sIn.Close();
            sOut.Close();
        }

How to create PROXY CLASS from WebSerivce

Open Visual Studio Command Prompt and run below

wsdl /out:myProxyClass.cs http://hostserver/application/service.asmx?WSDL

It will create a C# class in C Drive

HOW TO KNOW SQL QUERY EXECUTION TIME

--Track query execution time
DECLARE @EndTime   DATETIME
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()

--Query goes here
select * from My_Table

SET @EndTime = GETDATE()

PRINT 'StartTime = ' + CONVERT(VARCHAR(30),@StartTime,121)
PRINT '  EndTime = ' + CONVERT(VARCHAR(30),@EndTime,121)
PRINT ' Duration = ' + convert(varchar,DATEDIFF(ms, @StartTime, @EndTime))+' mill seconds'

GET GIVEN DATE FORMAT FOR GIVEN INPUT FORMAT

private string DateFormat(string DateValue, string DateFormat, string RequiredFormat)
        {
            string result = DateValue;
            IFormatProvider provider = CultureInfo.InvariantCulture;
            try
            {
                if (!string.IsNullOrEmpty(DateValue) && !string.IsNullOrEmpty(DateFormat))
                    result = DateTime.ParseExact(DateValue, DateFormat, provider).ToString(RequiredFormat);

            }
            catch (Exception ex)
            {              
                throw ex;
            }
            return result;
        }