Thursday, 28 March 2013

Pivot table in sql Server

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. 
UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
But we will see UNPIVOT afterwords.


Example :

Table Design :

VendorId         nvarchar(50)
IncomeDay nvarchar(50)
IncomeAmount int


Insert Some Data :


insert into testdb1.dbo.DailyIncome values ('Nihar', 'WED', 500)
insert into testdb1.dbo.DailyIncome values ('Rohan', 'SAT', 100)
insert into testdb1.dbo.DailyIncome values ('Nihar', 'SAT', 500)
insert into testdb1.dbo.DailyIncome values ('Nihar', 'THU', 800)
insert into testdb1.dbo.DailyIncome values ('Vishal', 'TUE', 600)
.....more...


Query :


select  *  from testdb1.dbo.DailyIncome
pivot (avg (IncomeAmount)
 for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) 
 as AvgIncomePerDay



Output -> 


VendorId MON TUE WED THU FRI SAT SUN
-----------------------------------------------------------------------
Nihar    500 350 500 800 900 500 400  
Rohan 300 600 900 800 300 800 600
Vishal 600 150 500 300 200 100 400








Wednesday, 27 March 2013

How to retrieve last 6 months data in sql server

How to retrieve last n months data in sql server ?


Query :


  SELECT *
  FROM [db1].[dbo].[Data]
  where  entry_date >= dateadd(mm,datediff(mm,0,getdate())-6,0)


Parameter :  Pink color marked data object worked as n number of months .

Output :

Display last 6 months data from current date.



How to retrieve Year / Month from Current or Any Datetime in SQL Server

Session 2 :


How to retrieve Year / Month from Current or Any Datetime ?


select 
[YEAR] = YEAR(getdate())
,[YEAR] = DATEPART(YY,getdate()) 
,[MONTH] = month(getdate())
,[MONTH] = DATEPART(mm,getdate())
,[MONTH NAME] = DATENAME(mm, getdate())


Output : -


YEAR YEAR MONTH    MONTH MONTH NAME
2013 2013 3            3                 March



Tuesday, 26 March 2013

System operations in C#


         

System.Diagnostics.Process

The Process class provides functionality to monitor system processes across the network, and to start and stop local system processes.
In additional to retrieving lists of running processes (by specifying either the computer, the process name, or the process ID) or viewing information about the process that currently has access to the processor, you can get detailed knowledge of process threads and modules both through the Process class itself, and by interacting with theProcessThread and ProcessModule classes.
The ProcessStartInfo class enables you to specify a variety of elements with which to start a new process, such as input, output, and error streams, working directories, and command line verbs and arguments. These give you fine control over the behavior of your processes.
Other related classes let you specify window styles, process and thread priorities, and interact with collections of threads and modules.
Some related classes:
  • Process: Provides access to local and remote processes and enables you to start and stop local system processes.
  • ProcessModule: Represents a .dll or .exe file that is loaded into a particular process.
  • ProcessModuleCollection: Provides a strongly typed collection of ProcessModule objects.
  • ProcessStartInfo: Specifies a set of values used when starting a process.
  • ProcessThread: Represents an operating system process thread.
  • ProcessThreadCollection: Provides a strongly typed collection of ProcessThread objects.

Some Operations :


              To ShutDown : shutdown -s

              To Restart : shutdown -r

              To Logoff : shutdown -l

              To Hibernate :%windir%\system32\rundll32.exe PowrProf.dll,SetSuspendState

              To Lock : Rundll32.exe User32.dll,LockWorkStation

              To Sleep : rundll32.exe powrprof.dll,SetSuspendState 0,1,0

            Example : ProcessStartInfo process = new ProcessStartInfo("shutdown.exe", "-s");

Examples:


            ProcessStartInfo process = new ProcessStartInfo("shutdown.exe", "-s");
            Process.Start(process);

            EventLog ev = new EventLog("Application", System.Environment.MachineName, "MyAppName");
            ev.WriteEntry("My event text", System.Diagnostics.EventLogEntryType.Information);
            ev.Close();

            StackTrace stc = new StackTrace(false);
            int framecount = stc.FrameCount;

            StackFrame stFrame = new StackFrame();
            Console.WriteLine("GetFrame method : " + stFrame.GetMethod());
            Console.WriteLine("GetFrame method : " + stFrame.GetMethod().Name.ToString());



            Process proces = new Process();
            proces = Process.Start("notepad.exe");
            proces.WaitForExit(1000);
            proces.CloseMainWindow();   // Closes main interface window

           //To Lock workstation
            Process.Start(@"C:\WINDOWS\system32\rundll32.exe","user32.dll,LockWorkStation");   

Monday, 25 March 2013

ObjectDataSource in asp.net

ASP.NET 2.0 ships with five built-in data source controls – SqlDataSourceAccessDataSourceObjectDataSourceXmlDataSource, and SiteMapDataSource – although you can build your own custom data source controls, if needed. Since we have developed an architecture for our tutorial application, we'll be using the ObjectDataSource against our BLL classes.





Step 1: Adding and Configuring the ObjectDataSource Control

Start by opening the SimpleDisplay.aspx page in the BasicReporting folder, switch to Design view, and then drag an ObjectDataSource control from the Toolbox onto the page's design surface. The ObjectDataSource appears as a gray box on the design surface because it does not produce any markup; it simply accesses data by invoking a method from a specified object. The data returned by an ObjectDataSource can be displayed by a data Web control, such as the GridView, DetailsView, FormView, and so on.
Note   Alternatively, you may first add the data Web control to the page and then, from its smart tag, choose the <New data source> option from the drop-down list.
To specify the ObjectDataSource's underlying object and how that object's methods map to the ObjectDataSource's, click on the Configure Data Source link from the ObjectDataSource's smart tag.
By coding 

   <asp:GridView ID="gvMainGrp" runat="server" AutoGenerateColumns="False" AutoGenerateSelectButton="True"           
                        AllowPaging="True" PageSize="5" CssClass="GridviewTable" RowStyle-Height="25"
                        AutoGenerateEditButton="True"
                        AutoGenerateDeleteButton="True"
                        RowStyle-Width="30" RowStyle-HorizontalAlign="Center" 
                        DataSourceID="ObjectDataSource1" AllowSorting="True" CellPadding="4" 
                        ForeColor="#333333" GridLines="None">
                        <Columns>
                            <asp:BoundField DataField ="process_Cd" HeaderText="cd"/>
                            <asp:BoundField DataField="description" HeaderText="Desc" />
                        </Columns>
                  <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <RowStyle BackColor="#EFF3FB" HorizontalAlign="Left" />
                        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <EditRowStyle BackColor="#2461BF" />
                        <AlternatingRowStyle BackColor="White" />
                        <RowStyle HorizontalAlign="Center" Height="25px" Width="30px"></RowStyle>
                    </asp:GridView>
                    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
                         DeleteMethod ="deleteProcessDetails" UpdateMethod="updateProcessDetails"
                        SelectMethod="getProcessDetails" TypeName="BusinessLayer.Product.processBiz">
                        <DeleteParameters>
                            <asp:Parameter Name="process_cd" Type="Int32" />
                        </DeleteParameters>
                        <UpdateParameters>
                            <asp:Parameter Name="process_cd" Type="Int32" />
                            <asp:Parameter Name="description" Type="String" />
                        </UpdateParameters>
                    </asp:ObjectDataSource>
                </td>


public DataTable getProcessDetails()
        {
            DataTable dtDetail = new DataTable();
            sqlDataaccess = new DataLayer.SQLDataAccess();
            System.Text.StringBuilder strQuery = new System.Text.StringBuilder();
            strQuery.Append("SELECT [Process_Cd],[description] FROM [abc].[dbo].[Temp_Process]");
            return sqlDataaccess.getDataTable(strQuery.ToString());
        }

        public void deleteProcessDetails(int process_cd)
        {
            DataTable dtDetail = new DataTable();
            sqlDataaccess = new DataLayer.SQLDataAccess();
            System.Text.StringBuilder strQuery = new System.Text.StringBuilder();
            strQuery.Append("delete FROM [abc].[dbo].[Temp_Process] where [Process_Cd]='" + process_cd + "'");
            sqlDataaccess.ExecuteNonQuery(strQuery.ToString());
        }

        public void updateProcessDetails(int process_cd, string description)
        {
            DataTable dtDetail = new DataTable();
            sqlDataaccess = new DataLayer.SQLDataAccess();
            System.Text.StringBuilder strQuery = new System.Text.StringBuilder();
            strQuery.Append("update  [abc].[dbo].[Temp_Process] set [description]='" + description + "' where [Process_Cd]='" + process_cd + "'");
            sqlDataaccess.ExecuteNonQuery(strQuery.ToString());
        }



Enjoy... check it....





Configure the ObjectDataSource Manually

The ObjectDataSource's Configure Data Source wizard offers a quick way to specify the object it uses and to associate what methods of the object are invoked. You can, however, configure the ObjectDataSource through its properties, either through the Properties window or directly in the declarative markup. Simply set the TypeNameproperty to the type of the underlying object to be used, and the SelectMethod to the method to invoke when retrieving data.
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
        SelectMethod="GetProducts"
        TypeName="ProductsBLL">
</asp:ObjectDataSource>
Even if you prefer the Configure Data Source wizard there may be times when you need to manually configure the ObjectDataSource, as the wizard only lists developer-created classes. If you want to bind the ObjectDataSource to a class in the .NET Framework – such as the Membership class, to access user account information, or the Directory classto work with file system information – you'll need to manually set the ObjectDataSource's properties.

Step 2: Adding a Data Web Control and Binding it to the ObjectDataSource

Once the ObjectDataSource has been added to the page and configured, we're ready to add data Web controls to the page to display the data returned by the ObjectDataSource's Select method. Any data Web control can be bound to an ObjectDataSource; let's look at displaying the ObjectDataSource's data in a GridView, DetailsView, and FormView.

Binding a GridView to the ObjectDataSource

Add a GridView control from the Toolbox to SimpleDisplay.aspx's design surface. From the GridView's smart tag, choose the ObjectDataSource control we added in Step 1. This will automatically create a BoundField in the GridView for each property returned by the data from the ObjectDataSource's Select method (namely, the properties defined by the Products DataTable).






User control in asp.net with Example


User control is powerful functionality of ASP.NET. With the help of the user control you can reuse the Design as well as code in the application. Developing User control is similar to developing form in ASP.NET. User control is created in markup file with .ascx extension.

We create two user control one for collect the user information and second for Display the user information.

Step 1:  Add the Web user control in your form with the name MyControl.ascx ( .ascx is extension for user-control)

ASCX code :

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="MyControl.ascx.cs" Inherits="UserControlDemo.MyControl" %>

<asp:TextBox ID="textNumber" runat="server" 
    ReadOnly="True" Width="32px" Enabled="False" />
<asp:Button Font-Bold="True" ID="buttonUp" runat="server"  
    Text="^" OnClick="buttonUp_Click" />
<asp:Button Font-Bold="True" ID="buttonDown" runat="server" 
    Text="v" OnClick="buttonDown_Click" />


ASCX.CS code (code behind of user-control page)

  public partial class MyControl : System.Web.UI.UserControl
    {        
        private int m_minValue;
        private int m_maxValue = 100;
        private int m_currentNumber = 0;
        public int MinValue
        {
            get
            {
                return m_minValue;
            }
            set
            {
                if (value >= this.MaxValue)
                {
                    throw new Exception("MinValue must be less than MaxValue.");
                }
                else
                {
                    m_minValue = value;
                }
            }
        }
        public int MaxValue
        {
            get
            {
                return m_maxValue;
            }
            set
            {
                if (value <= this.MinValue)
                {
                    throw new
                        Exception("MaxValue must be greater than MinValue.");
                }
                else
                {
                    m_maxValue = value;
                }
            }
        }
        public int CurrentNumber
        {
            get
            {
                return m_currentNumber;
            }
        }
        protected void Page_Load(Object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                m_currentNumber =
                    Int16.Parse(ViewState["currentNumber"].ToString());
            }
            else
            {
                m_currentNumber = this.MinValue;
            }
            DisplayNumber();
        }
        protected void DisplayNumber()
        {
            textNumber.Text = this.CurrentNumber.ToString();
            ViewState["currentNumber"] = this.CurrentNumber.ToString();
        }
        protected void buttonUp_Click(Object sender, EventArgs e)
        {
            if (m_currentNumber == this.MaxValue)
            {
                m_currentNumber = this.MinValue;
            }
            else
            {
                m_currentNumber += 1;
            }
            DisplayNumber();
        }
        protected void buttonDown_Click(Object sender, EventArgs e)
        {
            if (m_currentNumber == this.MinValue)
            {
                m_currentNumber = this.MaxValue;
            }
            else
            {
                m_currentNumber -= 1;
            }
            DisplayNumber();
        }
    }


ASPX Page


<%@ Register TagName="My" Src="~/MyControl.ascx" TagPrefix="Mycc" %>

(Declare Above line which include ascx src file and tag prefix )

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="true" 
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="Line_No" HeaderText="Line_No" 
                    SortExpression="Line_No" />
                <asp:BoundField DataField="Description" HeaderText="Description" 
                    SortExpression="Description" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:XYZ %>" 
            SelectCommand="SELECT [Line_No], [Description] FROM [Temp_Line]">
        </asp:SqlDataSource>
      
      <Mycc:My ID="Value" runat="server" MaxValue="10" MinValue ="0" />
    </div>
    </form>
</body>
</html>



Enjoy..check output..... 




Monday, 18 March 2013

NameValueCollection with Example in C#


NameValueCollection collection is based on the NameObjectCollectionBase class. However, unlike the NameObjectCollectionBase, this class stores multiple string values under a single key.
This class can be used for headers, query strings and form data.
Each element is a key/value pair.
Collections of this type do not preserve the ordering of element, and no particular ordering is guaranteed when enumerating the collection.
The capacity of a NameValueCollection is the number of elements the NameValueCollection can hold. As elements are added to a NameValueCollection, the capacity is automatically increased as required through reallocation.



Aspx :



    <table width="100%">
        <tr>
            <td align ="center">
                <table width ="50%">
                    <tr>
                        <td>
                            <asp:Label ID="Label1" runat="server" Text="Using All Key"></asp:Label>
                        </td>
                    </tr>
                     <tr>
                        <td>
                            <asp:GridView ID="gvAllKey" runat="server">                          
                            </asp:GridView>
                        </td>
                    </tr>
                     <tr>
                        <td>
                            <asp:Label ID="Label2" runat="server"  Text="Using Get Key"></asp:Label>
                        </td>
                    </tr>
                     <tr>
                        <td>
                            <asp:GridView ID="gvGetKeyNGet" runat="server">
                       
                            </asp:GridView>
                         
                        </td>
                    </tr>
                </table>
             
            </td>
        </tr>
    </table>




C#


protected void Page_Load(object sender, EventArgs e)
        {
            NameValueCollection myColl = new NameValueCollection();
            myColl.Add("Kalyani", "1");
            myColl.Add("Impact", "2");
            myColl.Add("IBM", "3");

            IList<ABC> anc = GetAllKeys(myColl);
            IList<ABC> abc = GetKeys(myColl);

            gvAllKey.DataSource = anc.AsEnumerable();
            gvAllKey.DataBind();

            gvGetKeyNGet.DataSource = abc.AsEnumerable();
            gvGetKeyNGet.DataBind();
        }

        public IList<ABC> GetAllKeys(NameValueCollection coll)
        {
            IList<ABC> _list = new List<ABC>();
            foreach (string item in coll.AllKeys)
            {
                ABC a = new ABC();
                a.Name = item.ToString();
                a.Value = coll[item].ToString();
                _list.Add(a);
            }
            return _list;
        }


        public IList<ABC> GetKeys(NameValueCollection coll)
        {
            IList<ABC> _list = new List<ABC>();
            for (int i = 0; i < coll.Count; i++)
            {
                ABC abc = new ABC();
                abc.Name = coll.GetKey(i).ToString();
                abc.Value = coll.Get(i).ToString();
                _list.Add(abc);
            }
            return _list;
        }


    }


    public class ABC
    {
        public string Name { get; set; }
        public string Value { get; set; }
    }

Thursday, 14 March 2013

File uploading and Downloading in asp.net

Here we are performing operations like " File Uploading and Downloading From Gridview ".

Table Structure :

ColumnName   Datatype

id           int
f_name   nvarchar(50)
fileSize           int
attachment   varbinary(MAX)
contentType   nvarchar(MAX)


ASPX Code: 


    <table width="100%">
            <tr>
                <td align="center">
                    <table width="50%">
                        <tr>
                            <td>
                                <asp:Label ID="Label3" runat="server" Text="Select"></asp:Label>
                            </td>
                            <td>
                                <asp:FileUpload ID="FileUpload1" runat="server" />
                            </td>
                        </tr>
                        <tr>
                            <td colspan="2">
                                <asp:Button ID="Button1" runat="server" Text="Save" OnClick="Button1_Click" />
                            </td>
                        </tr>
                        <tr><%--DataSourceID="SqlDataSource1"--%>
                            <td align="center" colspan="2">
                                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AutoGenerateSelectButton="true"
                                     DataKeyNames="id" OnSelectedIndexChanged="gvEmp_SelectedIndexChanged"
                                    OnRowCommand="gvEmpSearch_RowCommand" >
                                    <Columns>
                                        <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
                                            SortExpression="id" />
                                        <asp:BoundField DataField="f_name" HeaderText="f_name" SortExpression="f_name" />
                                        <asp:BoundField DataField="fileSize" HeaderText="fileSize" SortExpression="fileSize" />
                                        <asp:TemplateField AccessibleHeaderText="Download">
                                            <HeaderTemplate>
                                                <asp:Label ID ="lblHeader" runat="server" Text ="Attachment"></asp:Label>
                                            </HeaderTemplate>
                                            <ItemTemplate>
                                                <%-- <asp:HyperLink ID="lnkUrl" runat="server" Target="_blank" Text='<%# Eval("f_name") %>'
                                                    NavigateUrl='<%# Eval("attachment")%>'></asp:HyperLink>--%>
                                                <asp:LinkButton runat="server" CommandArgument="<%# ((GridViewRow)(Container)).RowIndex %>"
                                                    CommandName="name" ID="lnkname" Text='<%# bind("f_name")%>'></asp:LinkButton>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                    </Columns>
                                </asp:GridView>
                              <%--  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:testdb1ConnectionString %>"
                                    SelectCommand="SELECT * FROM [tblAttachment]"></asp:SqlDataSource>--%>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
        </table>



Class Code : 


string connection = ConfigurationManager.ConnectionStrings["testdb1ConnectionString"].ConnectionString;
        SqlConnection conn;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                bindGridData();
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connection);
            conn.Open();

            string path = FileUpload1.PostedFile.FileName.ToString();
            string FileName = FileUpload1.FileName.ToString();
            string FileSize = FileUpload1.PostedFile.ContentLength.ToString();
            string FileContent = FileUpload1.PostedFile.ContentType.ToString();
            string filename = Path.GetFileName(path);

            FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
            BinaryReader br = new BinaryReader(fs);
            Byte[] bytes = br.ReadBytes((Int32)fs.Length);
            br.Close();
            fs.Close();


            string strQuery = "INSERT INTO [testdb1].[dbo].[tblAttachment]([f_name],[fileSize],[attachment],[contentType]) VALUES(@f_name,@fileSize,@attachment,@contentType)";
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.Parameters.Add("@f_name", SqlDbType.VarChar).Value = FileName;
            cmd.Parameters.Add("@fileSize", SqlDbType.VarChar).Value = Convert.ToInt32(FileSize);
            cmd.Parameters.Add("@attachment", SqlDbType.Binary).Value = bytes;
            cmd.Parameters.Add("@contentType", SqlDbType.VarChar).Value = FileContent;
            InsertUpdateData(cmd);
            bindGridData();

            conn.Close();
        }


        public void bindGridData()
        {
            conn = new SqlConnection(connection);
            conn.Open();
            string strquery = "select * from [testdb1].[dbo].[tblAttachment] ";
            DataTable dt = new DataTable();
            dt = getDataTable(strquery);
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }



        private Boolean InsertUpdateData(SqlCommand cmd)
        {
            String strConnString = System.Configuration.ConfigurationManager
            .ConnectionStrings["testdb1ConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
                return false;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        protected void gvEmp_SelectedIndexChanged(object sender, EventArgs e)
        {
            int i = Convert.ToInt32(GridView1.SelectedRow.Cells[1].Text);

            conn = new SqlConnection(connection);
            conn.Open();

            string strquery = "select * from [testdb1].[dbo].[tblAttachment] where id='" + i + "' ";

            DataTable dt = new DataTable();

            dt = getDataTable(strquery);




            conn.Close();
        }


        protected void gvEmpSearch_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "name")
            {
                conn = new SqlConnection(connection);
                int index = Convert.ToInt32(e.CommandArgument);
                //GridViewRow selectedRow = GridView1.Rows[index];
                string EmpId = GridView1.DataKeys[index].Value.ToString().Trim();
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "select * from [testdb1].[dbo].[tblAttachment] where id=@id";
                cmd.Parameters.AddWithValue("@id", EmpId);
                cmd.Connection = conn;
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    Response.ContentType = dr["contentType"].ToString();
                    Response.AddHeader("Content-Disposition", "attachment;filename=\"" + dr["f_name"] + "\"");
                    Response.BinaryWrite((byte[])dr["attachment"]);
                    Response.End();
                }
                conn.Close();
            }
        }



        public DataTable getDataTable(string query)
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Open();
            SqlCommand cmd1 = new SqlCommand(query, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd1);
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;

        }



Web.Config :

Write your connection string in web.config

Enjoy..... Happy Coding....





File operations in C# (.net)


Here we are performing some basic file operations which widely needed in applications.

ASPX :

    <div>
        <asp:Button ID="Button1" runat="server" Text="CLick" onclick="Button1_Click" />
        <asp:FileUpload ID="FileUpload1" runat="server" /><br /><br />     
    </div>

C# :
       using System.IO;
       using System.Text;


        protected void Button1_Click(object sender, EventArgs e)
        {

            string path = @"E:\MVC Application Demos\ABC.txt";            // File Path taken

            string name = FileUpload1.FileName.ToString();                // Here we get File Name only

            string path1 = FileUpload1.PostedFile.FileName.ToString();    // Here we get File Path only

            if (File.Exists(path.ToString()))
            {          
           //     ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('File Already exists');", true);
            }
            else
            {
              // Create File Code
            }

            /*  Write File , which stored @remote location  */
            using (FileStream fs = File.Create(path, 1024))
            {
                Byte[] info = new UTF8Encoding(true).GetBytes("This is some text in the file.");    // Add some information to the file.              
                fs.Write(info, 0, info.Length);
            }



            /* Create a file to write to. */
            using (StreamWriter sw = File.CreateText(path))
            {
                sw.WriteLine("Hello");
                sw.WriteLine("And");
                sw.WriteLine("Welcome");
            }


            /* This text is always added, making the file longer over time
             if it is not deleted. */
            using (StreamWriter sw = File.AppendText(path))
            {
                sw.WriteLine("This");
                sw.WriteLine("is Extra");
                sw.WriteLine("Text");
            }              


            File.SetCreationTime(path, new DateTime(1857, 1, 1));       /* Set creation time of file  */
            File.SetLastAccessTime(path, new DateTime(1985, 5, 4));     /* Set last access time of file  */
            File.SetLastWriteTime(path, new DateTime(1985, 4, 3));      /* Set last write time of file  */   

        }


Reference :

 1)  File Members

 2)  File Methods

Wednesday, 13 March 2013

Stopwatch in c#

Stopwatch measures time elapsed. It is useful for micro-benchmarks in code optimization. It can perform routine and continuous performance monitoring. The Stopwatch type, found in System.Diagnostics, is useful in many contexts.


Example

First, Stopwatch is a class in the .NET Framework that is ideal for timing any operation in your C# programs. You must create Stopwatch as an instance. This makes it useful in multi-threaded applications or websites.
Program that uses Stopwatch [C#]

using System;
using System.Diagnostics;
using System.Threading;

class Program
{
    static void Main()
    {
 // Create new stopwatch
 Stopwatch stopwatch = new Stopwatch();

 // Begin timing
 stopwatch.Start();

 // Do something
 for (int i = 0; i < 1000; i++)
 {
     Thread.Sleep(1);
 }

 // Stop timing
 stopwatch.Stop();

 // Write result
 Console.WriteLine("Time elapsed: {0}",
     stopwatch.Elapsed);
    }
}

Output

Time elapsed: 00:00:01.0001477
Ref:
MSDN : Stopwatch Class