SQL Bulk Copy with C#.Net

 string strConnection = ConfigurationManager.ConnectionStrings["Connection"].ToString();
 SqlConnection con = new SqlConnection(strConnection);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from TableName", con);
        cmd.Connection = con;
        SqlDataReader dr = cmd.ExecuteReader();

        SqlConnection con2 = new SqlConnection(strConnection);
        con2.Open();
        SqlBulkCopy copy = new SqlBulkCopy(con2);
     
        copy.DestinationTableName = "DestinationTable";
        copy.WriteToServer(dr);
        dr.Close();
        con2.Close();
        con.Close();

Executing Stored Procedures in Parallel using ADO.NET

string Conn = ConfigurationManager.ConnectionStrings["Connection"].ToString();

SqlConnection con = new SqlConnection(Conn);
        SqlCommand cmd1;
        SqlCommand cmd2;
        SqlDataReader reader1;
        SqlDataReader reader2;
        try
        {
            if (!IsPostBack)
            {

                cmd1 = new SqlCommand("Sp Name", con);
                cmd1.CommandType = CommandType.StoredProcedure;
                con.Open();
                reader1 = cmd1.ExecuteReader();
                if (reader1.Read())
                {
                    grd_View.DataSource = reader1;
                    grd_View.DataBind();
                }
               if (!reader1.IsClosed)
                {
                    reader1.Close();
                }

                cmd2 = new SqlCommand("Sp Name", con);
                cmd2.CommandType = CommandType.StoredProcedure;

                reader2 = cmd2.ExecuteReader();
                if (reader2.Read())
                {
                    GridView1.DataSource = reader2;
                    GridView1.DataBind();
                }
                if (!reader2.IsClosed)
                {
                    reader2.Close();
                }
             
            }
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            con.Close();
        }

Copy a table into new table with/without data - SQL Server

Lets see how to copy an existing table to new table in SQL Server. There are two options. They are

• Copy only the structure of an existing table into new table
  

• Copy only the structure with data of an existing table into new table

Copy only the structure of an existing table into new table:

SELECT * INTO  NewTable  FROM OldTable WHERE 3=4
The above query will copy the structure of  an existing table(OldTable ) into the new table(NewTable).

Copy only the structure with data of an existing table into new table:

SELECT * INTO NewTable  FROM OldTable

This is also same like the previous query, but it copies the structure of existing table(OldTable) with data as well into the new table(NewTable)


When we write  SELECT 3 + NULL
The output is null

When we write SELECT * FROM TableName WHERE 1=1

It will display entire table data

phone number validation code in javascript

Example :(080) 235-4354

 function PhoneNumberValidation(e) {
            var keynum;
            var keychar;
            // For Internet Explorer
            if (window.event) {
                keynum = e.keyCode;
            }
            // For Netscape/Firefox/Opera
            else if (e.which) {
                keynum = e.which;
            }
            keychar = String.fromCharCode(keynum)
            var r = new RegExp("[0-9]", "g");
            if (keychar.match(r) == null) {
                return false;
            }
            else {
                var getValue = document.getElementById('ctl00_ContentPlaceHolder1_txtPhone').value;
                var s = getValue.length;
                if (s == 3) {
                    document.getElementById('ctl00_ContentPlaceHolder1_txtPhone').value = '(' + document.getElementById('ctl00_ContentPlaceHolder1_txtPhone').value + ') ';
                }
                if (s == 9) {
                    document.getElementById('ctl00_ContentPlaceHolder1_txtPhone').value = document.getElementById('ctl00_ContentPlaceHolder1_txtPhone').value + '-';
                }
                return true;
            }
        }

 <td>
  <asp:TextBox ID="txtPhone" runat="server" MaxLength="14" onkeypress="return PhoneNumberValidation(event)"></asp:TextBox>

</td>

Validate multiple email id's using javascript?

<script type="text/javascript">

        function isUrl(s) {

            var regexp = /\w+([-+.]\w+)*\w+([-.]\w+)*\.\w+([-.]\w+)*/
            return regexp.test(s);
        }


        function trim(text) {

            return text.replace(/^\s+|\s+$/g, "");
        }

        function checkUrls(sender, args) {

            var urls = document.getElementById('<%=txtMisspellvalue.ClientID %>').value;

            if (document.getElementById('<%=txtMisspellvalue.ClientID %>').value == null
                     || document.getElementById('<%=txtMisspellvalue.ClientID %>').value == "") {
                args.IsValid = false;
                return;
            }
            else {
                var textarea = document.getElementById('<%=txtMisspellvalue.ClientID %>');
                var isOk = true;

                var urlsArr = textarea.value.replace(/\r\n/g, "\n").split("\n");

                for (i = 0; i < urlsArr.length; i++) {
                    if (urlsArr[i] != '') {
                        if (isUrl(trim(urlsArr[i]))) {
                            isOk = true;
                        }
                        else {
                            isOk = false;
                        }
                    }
                }
                if (isOk === true) {
                    args.IsValid = true;
                }
                else {
                    args.IsValid = false;
                }

            }
        }
    </script>


<table>
<tr>

  <td>

 <asp:TextBox ID="txtvalue" runat="server" Width="195" Height="100" TextMode="MultiLine"></asp:TextBox>

 <asp:CustomValidator ID="CSvtxtvalue" runat="server" ControlToValidate="txtvalue"
 ClientValidationFunction="checkUrls" Display="None" ErrorMessage="" ValidateEmptyText="true"></asp:CustomValidator>

<cc1:ValidatorCalloutExtender ID="vce_txtvalue" runat="server" TargetControlID="CSvtxvalue">
                                        </cc1:ValidatorCalloutExtender>

</td>
</tr>
</table>

Clock on webpage using server and system time?

Default.aspx page
<table>
            <tr>
                <td>
                    Time: <span id="TimeDisplay"></span>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
                </td>
            </tr>
   </table>

Default.aspx.cs file

 protected void Page_Load(object sender, EventArgs e)
    {
        Label1.Text = Convert.ToString(DateTime.Now.TimeOfDay);
        Response.Write("<script>var timeServer = new Date('" + DateTime.Now.ToString() + "');</script>");
       

         const string crlf = "\r\n";
        string jsscript = "<script type='text/javascript'>"
            + crlf + "window.onload=startclock;" + crlf + "var clock;" + crlf + "var time_diff;" + crlf +
            "function startclock(){" + crlf + "clock=document.getElementById('TimeDisplay');"
            + crlf + "server_time = new Date('" + WelcomeDetails.Rows[0]["CurrentTime"] + "');" +
            crlf + "time_diff=new Date()-server_time;" + crlf + "setInterval('runclock()',1000);" + crlf + "}"
            + crlf + "function runclock(){" + crlf +
            "var cDate=new Date();" + crlf +
            "cDate.setTime(cDate.getTime()-time_diff);" + crlf +
            "var DateString = cDate.format('dd MMMM yyyy');" + crlf +
            "var tmr ='Time: ';" + crlf +
            "var curr_hours = cDate.getHours();" + crlf +
            "var timeofDay = (curr_hours < 12) ? 'AM' : 'PM';" + crlf +
            "curr_hours = (curr_hours > 12) ? curr_hours - 12 : curr_hours;" + crlf +
            "curr_hours = (curr_hours == 0) ? 12 : curr_hours;" + crlf +
            "var curr_mins = cDate.getMinutes();" + crlf +
            "var curr_secs = cDate.getSeconds();" + crlf +
            "curr_hours=(curr_hours < 10)?'0' + curr_hours:curr_hours;" + crlf +
            "curr_mins=(curr_mins < 10)?'0' + curr_mins:curr_mins;" + crlf +
            "curr_secs=(curr_secs < 10)?'0' + curr_secs:curr_secs;" + crlf +
        "clock.innerHTML=tmr + DateString+', '+curr_hours+':'+curr_mins+':'+curr_secs +' '+timeofDay+' (CST)'" + crlf + "}" +
            crlf + "</script>";

        Page.RegisterStartupScript("ServerTimeScript", jsscript);
     
    }

Create a Pivot Table from a DataTable using C# in Asp.net

Normal Table

CustomerName   Value
  Vinod                    25
  Sagar                     35
  Manu                     45

Pivot Table

Vinod    Sagar   Manu
   25           35        45


Public DataTable Pivot()
{
                DataTable dt = new DataTable();
                dt = "Fetch Data from Database"
                try
                {
                    DataTable dtData = new DataTable();
                    DataColumn col;
                    DataRow newRow;
                    for (int I = 0; I < dt.Columns.Count - 1; I++)
                    {
                        newRow = dtData.NewRow();
                        for (int J = 0; J < dt.Rows.Count; J++)
                        {
                         col = new DataColumn(dt.Rows[J][I].ToString(),Type.GetType("System.String"));
                            dtData.Columns.Add(col);
                        }
                    }

                    for (int I = 1; I <= dt.Columns.Count - 1; I++)
                    {
                        newRow = dtData.NewRow();
                        for (int J = 0; J < dt.Rows.Count; J++)
                        {
                            newRow[J] = dt.Rows[J][I].ToString();
                        }
                        dtData.Rows.Add(newRow);
                    }

                     return dtData;

}

How to pivot a normal sql query with dynamic columns

Normal Table

id      Name    Marks   ddd
1    aaa    20    1
2    bbb    35    2
3    ccc    12    3
4    ddd    32    4
5    test    35    5

pivot Table

aaa     bbb     ccc     ddd     test
20    NULL    NULL    NULL    NULL
NULL    35    NULL    NULL    NULL
NULL    NULL    12    NULL    NULL
NULL    NULL    NULL    32    NULL
NULL    NULL    NULL    NULL    35


select * from dbo.[auto]
select aaa,bbb,ccc,ddd,test from dbo.[auto]
pivot(max(marks) for name in(aaa,bbb,ccc,ddd,test))as p

sorting and paging with gridview asp.net

protected void gv_Sorting(object sender, GridViewSortEventArgs e)
    {
        string sortExpression = e.SortExpression;

        if (GridViewSortDirection == SortDirection.Ascending)
        {
            GridViewSortDirection = SortDirection.Descending;
            SortGridView(sortExpression, DESCENDING);
        }
        else
        {
            GridViewSortDirection = SortDirection.Ascending;
            SortGridView(sortExpression, ASCENDING);
        }
    }
    public SortDirection GridViewSortDirection
    {
        get
        {
            if (ViewState["sortDirection"] == null)
                ViewState["sortDirection"] = SortDirection.Ascending;

            return (SortDirection)ViewState["sortDirection"];
        }
        set { ViewState["sortDirection"] = value; }
    }
    private void SortGridView(string sortExpression, string sortDirection)
    {
        try
        {
            DataView dv = new DataView((DataTable)ViewState["gvDetails"]);
            dv.Sort = sortExpression + " " + sortDirection;
            ViewState["gvDetails "] = dv.ToTable();
            gv.DataSource = dv;
            gv.DataBind();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Union,Except and Intersect operator in Linq

char[] delimiters = new char[] { '\n', '\r', ',' };
                string[] strNewValue = txtvalue.Text.Trim().Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
                string OldString = ViewState["OldMisspell"].ToString();
                string[] strOld = OldString.Trim().Split(delimiters, StringSplitOptions.RemoveEmptyEntries);

                DataTable dtMissingValue = new DataTable();
                dtMissingValue.Columns.Add("ColumnName", typeof(string));
                dtMissingValue.Columns.Add("ColumnName ", typeof(string));
                dtMissingValue.Columns.Add("ColumnName ", typeof(int));
                dtMissingValue.Columns.Add("ColumnName ", typeof(bool));

      var vDeleteingValues = OldString.Except(strNewValue).Select(s => new { ColumnName = s, ColumnName = 1 });
      var vNewValues = strNewValue.Except(strOld).Select(a => new { ColumnName = a, ColumnName = 0 });
      var vOldValues = strNewValue.Intersect(strOld).Select(s => new { ColumnName = s, ColumnName = 0 });

                var vBindingData = vNewValues.Union(vOldValues).Union(vDeleteingValues);
                foreach (var item in vBindingData
                {
                    DataRow dtrow = dtMissingValue.NewRow();
                    dtrow["Domain"] = txt.Text;
                    dtrow["Misspellvalue"] = item.ColumnName;
                    dtrow["CreatedBy"] = ID;
                    dtrow["IsHidden"] = item.ColumnName;
                    dtMissingValue.Rows.Add(dtrow);
                }

Read And Write Text in text file in asp.net using C sharp

try

        {

            StringBuilder obj1 = new StringBuilder();

            string sCon = string.Empty;

            string scon1 = string.Empty;

            using (StreamReader sr = new StreamReader("file path" + "file name"))

            {

                sCon = sr.ReadToEnd();

            }

            obj1.Append(sCon.Substring(0, sCon.IndexOf("#spoint") + 11) + Environment.NewLine);

            int startpoint = sCon.IndexOf("#spoint") + 11;

            int endpoint = sCon.IndexOf("#epoint");

            string Test = sCon.Substring(startpoint, endpoint - startpoint);

            if (Test.IndexOf("line Name ") > -1)

            {

                obj1.Append(Test);

            }

            obj1.Append("New string append" + Environment.NewLine);

            obj1.Append(sCon.Substring(sCon.IndexOf("#epoint")) + Environment.NewLine);

            scon2 = obj1.ToString();

           

            FileStream fsPattern = File.OpenWrite("file path" + "file name");

            Encoding encPattern = Encoding.ASCII;

            byte[] restPattern = encPattern.GetBytes(obj1.ToString());

            fsPattern.Write(restPattern, 0, objPattern.Length);

            fsPattern.Flush();

            fsPattern.Close();

            fsPattern.Dispose();

        }

        catch (Exception ex)

        {

        }

Post Data To Remote Server in asp.net

try
        {
            using (StreamReader reader = new StreamReader("Give file path" + "file name"))
            {
                filecontent = reader.ReadToEnd();
            }
            postData = @"file=" + HttpUtility.UrlEncode(filecontent) + "&submit=save";
            byte[] data = Encoding.ASCII.GetBytes(postData);
            HttpWebRequest webRequest = (HttpWebRequest)WebRequest.Create("Remote Server path");
            webRequest.Method = "POST";
            webRequest.ContentType = "application/x-www-form-urlencoded";
            webRequest.ContentLength = data.Length;
            Stream dataStream = webRequest.GetRequestStream();
            dataStream.Write(data, 0, data.Length);
            dataStream.Close();

            HttpWebResponse webResponse;
            webResponse = (HttpWebResponse)webRequest.GetResponse();
            Stream answer = webResponse.GetResponseStream();
            StreamReader _answer = null;
            _answer = new StreamReader(answer);
            string ResponseStatus = _answer.ReadToEnd();
               
        }
        catch (Exception ex)
        {           
        }

Download data using Web Browser in asp.net

private void runBrowserThread(string url)
        {
            try
            {
                var th = new Thread(() =>
                {
                    var br = new WebBrowser();
                    br.DocumentCompleted += browser_DocumentCompleted;
                    br.Navigate(url);
                    Application.Run();
                });
                th.SetApartmentState(ApartmentState.STA);
                th.Start();
            }
            catch (Exception ex)
            {

            }
        }
#region - Browser Event -
        void browser_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
        {           
            try
            {
                string Downloaddata = string.Empty;
                HtmlDocument doc = ((WebBrowser)sender).Document;
                HtmlElementCollection textboxes = doc.GetElementsByTagName("textarea");
                foreach (HtmlElement textbox in textboxes)
                {
                    Downloaddata = textbox.InnerText;
                    Downloaddata = Downloaddata + "";
                }
                File.WriteAllText("Give your path" + "file Name", Downloaddata);
            }
            catch (Exception ex)
            {

               
            }
        }

Roll Back Deleted Data in sql

We shoule have Full permission of Database

 Select [RowLog Contents 0] FROM   sys.fn_dblog(NULL, NULL) WHERE  AllocUnitName = 'dbo.Employee'     
   AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' ) AND Operation in ( 'LOP_DELETE_ROWS' )

Check Duplicate And Delete Duplicate Rows in sql server

We can remove duplicate values in sql server using table expression and row_number

Select WebAddress,COUNT(*) from dbo.CompanyInformation

group by WebAddress having COUNT(*)>1;

With CTS As

(

Select ROW_NUMBER()over(partition by Company order by Company)as Rowid,WebAddress

from dbo.CompanyInformation

)

 select * from CTS where WebAddress='www.oracle.com'

 --delete from CTS where Rowid >1;