Posts

Showing posts from October, 2012

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.Re…

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 tableCopy 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 =…

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");

 …

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_di…

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++)
                  …

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"])…

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…

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

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 _answ…

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)
                {
                    Downloadda…

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

Image
We can remove duplicate values in sql server using table expression and row_number
SelectWebAddress,COUNT(*)fromdbo.CompanyInformation groupbyWebAddresshavingCOUNT(*)>1; WithCTSAs ( SelectROW_NUMBER()over(partitionbyCompanyorderbyCompany)asRowid,WebAddress fromdbo.CompanyInformation ) select*fromCTSwhereWebAddress='www.oracle.com' --delete from CTS where Rowid >1;