SqlClient - Read Data From database and store into DataTable or DataSet - C#

This tutorial show how to read database and pass the value in DataTable or DataSet. 

Requirement in this tutorial are :

1. using System.Data.SqlClient;
2. using System.Data;

The sample code should be like this :

            DataTable table = new DataTable();
            SqlConnection dbConn = new SqlConnection("<connection string>");
            SqlCommand dbComm = new SqlCommand("<sql statement or procedure name>", dbConn);
            
            //set command type is procedure if you are using store procedure
            //else remove this line
            dbComm.CommandType = CommandType.StoredProcedure;

            try
            {
                SqlDataAdapter dbAdapter = new SqlDataAdapter(dbComm);
                dbConn.Open();
                dbAdapter.Fill(table);
            }
            catch (SqlException sqlEx)
            {
                MessageBox.Show(sqlEx.Message);                
            }
            finally
            {
                dbConn.Close();                
            }  

Now change to store value in DataSet .

            DataSet dataSetTable = new DataSet();
            SqlConnection dbConn = new SqlConnection("<connection string>");
            SqlCommand dbComm = new SqlCommand("<sql statement or procedure name>", dbConn);
            
            //set command type is procedure if you are using store procedure
            //else remove this line
            dbComm.CommandType = CommandType.StoredProcedure;

            try
            {
                SqlDataAdapter dbAdapter = new SqlDataAdapter(dbComm);
                dbConn.Open();
                dbAdapter.Fill(dataSetTable,"<set table name here>");
            }
            catch (SqlException sqlEx)
            {
                MessageBox.Show(sqlEx.Message);                
            }
            finally
            {
                dbConn.Close();                
            }    

By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

Read More...

How to detect page refresh in asp.net

Page refresh is a one challenges that all web developers need to faces. It is because, when you have a button, and user click on the button and the process actually still processed at server side but the user refresh the page. The browser will ask user is either to resubmit or not. Normal user will always click resubmit. So the process actually repeat at server side also. This will cause a duplicate or redundant activity at server side which is will lead to incorrect result.

Follow this example to detect the page refresh in ASP.NET .

1. Create sample page name DetectPageRefresh.aspx 

We will do logic to detect page refresh at the code behind.

The  DetectPageRefresh.aspx  Page

  <asp:Label ID="Label1" runat="server"></asp:Label>

    <br />
    <br />
    <br />
    <asp:Button ID="Button1" runat="server" Text="Do postback at server side" 
        onclick="Button1_Click" />

The Code Behind

 private bool IsPageRefresh = false;
        public bool IsPageRefresh1
        {
            get { return IsPageRefresh; }
            set { IsPageRefresh = value; }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                ViewState["postids"] = System.Guid.NewGuid().ToString();
                Session["postid"] = ViewState["postids"].ToString();
            }
            else
            {
                if (ViewState["postids"].ToString() != Session["postid"].ToString())
                {
                    IsPageRefresh = true;
                }
                else
                {
                    IsPageRefresh = false;
                }
                Session["postid"] = System.Guid.NewGuid().ToString();
                ViewState["postids"] = Session["postid"].ToString();
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            if (!IsPageRefresh1)
            {
                Label1.Text = "Page do normal postback";
            }
            else
            {
                Label1.Text = "Page refresh";
            }
        }

The output

1. Click on the button.
2. and then press F5 button to refresh page

By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

Read More...

How To Show All session Key and Value In ASP.NET

Session is a  powerful feature in web application to store information in server memory. by doing this, any data will be save temporary in server memory and can be access in the application.

This is a advantage and disadvantage of Session :

Advantages:

• It helps maintain user state and data all over the application.
• It is easy to implement and we can store any kind of object.
• Stores client data separately.
• Session is secure and transparent from the user.

Disadvantages:

• Performance overhead in case of large volumes of data/user, because session data is stored in server memory.
• Overhead involved in serializing and de-serializing session data, because in the case of StateServer and SQLServer session modes, we need to serialize the objects before storing them.

 More about session can read here

In this example, i will show how to create simple page to show all session key and value of session key. :

Requirement

• aspx page and code behind
  

The Aspx Page

     <h1>
        Print All Session Key And Value</h1>
    <br />
    <asp:Panel ID="Panel1" runat="server">
    </asp:Panel> 

The Code Behind

protected void Page_Load(object sender, EventArgs e)
        {
            String table = "";
            table += "<table style=\"width:450px; border:1px solid #ccc;\"><tr>";
            table += "<th>Session Key</th>";            
            table += "<th>Session Value</th>";
            table += "</tr>";
   
            for (int i = 0; i < Session.Keys.Count; i++)
            {
                table += "<tr>";
                table += "<td>" + Session.Keys[i] + "</td>";                
                table += "<td>" + Session[i].ToString() + "</td>";
                table += "</tr>";              
            }

            table += "</table>";

            Panel1.Controls.Add(new LiteralControl(table));
        }

Sample Output

By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

Read More...

Background Worker Example in C#

How to avoid application "not responding" ..? if you developed one application  for example require to read some big data in database, read big file, or do some complicated task, you need to execute that task in a background worker.

Background worker allow program do the task in a background process like picture above and wont let your application Not responding but actually the program is still work.

The BackgroundWorker class allows you to run an operation on a separate, dedicated thread. Time-consuming operations like downloads and database transactions can cause your user interface (UI) to seem as though it has stopped responding while they are running. When you want a responsive UI and you are faced with long delays associated with such operations, the BackgroundWorker class provides a convenient solution.

Read more about BackgroundWorker here

Here the example :

The Code Behind

 private void button1_Click(object sender, EventArgs e)
        {
            BackgroundWorker bgw = new BackgroundWorker();
            bgw.DoWork += new DoWorkEventHandler(bgw_DoWork);
            bgw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bgw_RunWorkerCompleted);

            bgw.RunWorkerAsync();
            
        }

        void bgw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            //Do process after process complete
            //Example : you can do like close progress bar here
            toolStripStatusLabel1.Text = "Background Worker Complete the task";
        }

        void bgw_DoWork(object sender, DoWorkEventArgs e)
        {
            //Do work process here.        
            toolStripStatusLabel1.Text = "Background Worker do their work now";
            Thread.Sleep(5000);//sleep 10 seconds
        }

The Output

By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

Read More...

All About GridView CRUD without 'C' (Read,Update,Delete) function ASP.Net

Gridview is a powerful tools for developer to view the records from database, for example like user manager page.

Today i want to show how to delete, and modify in gridview plus to have a user friendly viewing functionality about the a Total records that user want to show on GridView and total records of all data.

Lets Try..

The ASP.NET Page

 <asp:Label ID="LErrorMessage" ForeColor="Red" runat="server"></asp:Label>
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CssClass="mainTable"
        BorderColor="Gray" BorderStyle="Solid" BorderWidth="1px" 
        AllowPaging="True" PageSize="15"
        Width="864px" EmptyDataText="Tiada Rekod Dijumpai" OnRowDeleting="GridView1_RowDeleting"
        OnPageIndexChanging="GridView1_PageIndexChanging" OnRowDataBound="addJavascript"
        OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
        <RowStyle BorderColor="Gray" BorderStyle="Solid" BorderWidth="1px" />
        <AlternatingRowStyle BackColor="#F7F6F3" BorderColor="Gray" BorderStyle="Solid" BorderWidth="1px"
            ForeColor="#333333" />
        <Columns>
            <asp:TemplateField HeaderText="No.">
                <ItemTemplate>
                    <%# Container.DataItemIndex + 1 %>
                </ItemTemplate>
                <ItemStyle Width="20px" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
            </asp:TemplateField>
            <asp:BoundField DataField="UserId" HeaderText="User ID">
                <ItemStyle Width="100px" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
            </asp:BoundField>
            <asp:BoundField DataField="First_Name" HeaderText="First Name">
                <ItemStyle Width="150px" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
            </asp:BoundField>
            <asp:BoundField DataField="Last_Name" HeaderText="Last Name">
                <ItemStyle Width="100px" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
            </asp:BoundField>
            <asp:BoundField DataField="IsLockedOut" HeaderText="is Lock">
                <ItemStyle Width="80px" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
            </asp:BoundField>
            <asp:CommandField CausesValidation="False" ShowDeleteButton="True">
                <ItemStyle Width="50px" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
            </asp:CommandField>
            <asp:CommandField SelectText="Modify" CausesValidation="false" ShowSelectButton="True">
                <ItemStyle Width="50px" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
            </asp:CommandField>
        </Columns>
        <HeaderStyle ForeColor="White" BackColor="#6699CC" Font-Bold="true" />
        <PagerStyle BackColor="#6699CC" ForeColor="White" />
    </asp:GridView>
    <br />
    <br />
    <asp:Panel ID="PanelDropDownGVPage" runat="server" Visible="false">
        Total users per page :
        <asp:DropDownList ID="DropDownList1" CssClass="defaultSelect" Width="50px" runat="server"
            AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
        </asp:DropDownList>
        <br />
    </asp:Panel>
    Shows records
    <asp:Literal ID="LRekodShow" runat="server"></asp:Literal>&nbsp; from&nbsp;
    <asp:Literal ID="LiteralTotalRekod" runat="server"></asp:Literal>&nbsp;users
    <br />

The JavaScript - put in header

   <script type="text/javascript">
       function ConfirmOnDelete(item) {
           if (confirm("Are you sure to remove " + item + "?") == true)
               return true;
           else
               return false;
       }
   </script>

The Code Behind

protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                BindGridview();
            }
        }

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            BindGridview();
        }

        private void BindGridview()
        {
            string err = "";
            DataTable UsersTable = GetAllUsersInformation(ref err);
            if (err.Trim() != "")
            {
                LErrorMessage.Text = err;
                return;
            }

            int pageIndex = GridView1.PageIndex;

            if (UsersTable.Rows.Count > GridView1.PageSize)
            {
                PanelDropDownGVPage.Visible = true;
                if (pageIndex == 0)
                {
                    LRekodShow.Text = "1 - " + GridView1.PageSize.ToString();
                }
                else
                {
                    int pageSize = GridView1.PageSize;
                    int numberRecord = pageIndex * pageSize;
                    int balance = UsersTable.Rows.Count - numberRecord;
                    int startRecordToShow = numberRecord + 1;
                    if (balance > GridView1.PageSize)
                    {

                        LRekodShow.Text = startRecordToShow.ToString() + " - " + (GridView1.PageSize + numberRecord).ToString();
                    }
                    else
                    {
                        LRekodShow.Text = startRecordToShow.ToString() + " - " + (balance + numberRecord).ToString();
                    }
                }
            }
            else if (UsersTable.Rows.Count == 0)
            {
                LRekodShow.Text = "0";
            }
            else
            {
                LRekodShow.Text = "1 - " + UsersTable.Rows.Count.ToString();
            }


            GridView1.DataSource = UsersTable;
            GridView1.DataBind();            

            string[] listAll = { "10", "15", "25", "35", "50" };
            DropDownList1.DataSource = listAll;
            DropDownList1.DataBind();

            DropDownList1.SelectedValue = GridView1.PageSize.ToString();


            LiteralTotalRekod.Text = UsersTable.Rows.Count.ToString();

        }

        private DataTable GetAllUsersInformation(ref string err)
        {
            DataTable users = new DataTable();

            users.Columns.Add("UserId", typeof(string));
            users.Columns.Add("First_Name", typeof(string));
            users.Columns.Add("Last_Name", typeof(string));
            users.Columns.Add("IsLockedOut", typeof(string));

            DataRow row;
            for (int i = 0; i < 100; i++)
            {
                row = users.NewRow();
                row["UserId"] = "AHMAD" + i.ToString();
                row["First_Name"] = "AHMAD FIRST NAME" + i.ToString();
                row["Last_Name"] = "AHMAD LAST NAME" + i.ToString();
                row["IsLockedOut"] = "FALSE";
                users.Rows.Add(row);
            }


            return users;
        }

        protected void addJavascript(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowState != DataControlRowState.Edit) // check for RowState
            {
                if (e.Row.RowType == DataControlRowType.DataRow) //check for RowType
                {
                    string id = e.Row.Cells[1].Text; // Get the id to be deleted
                    LinkButton lb = (LinkButton)e.Row.Cells[5].Controls[0]; //cast the ShowDeleteButton link to linkbutton
                    if (lb != null)
                    {
                        //add script to link button
                        lb.Attributes.Add("onclick", "return ConfirmOnDelete('User " + id + "');"); //attach the JavaScript function with the ID as the paramter
                    }
                }
            }
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            string err = "";
            //string UserID = GridView1.Rows[e.RowIndex].Cells[1].Text;
            string UserID = e.Values["UserId"].ToString();
            //do logic delete based on user id
        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //modifi user action
            //get user id from selected row
            string UserID = GridView1.SelectedRow.Cells[1].Text;
            //do logic to modify user
        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string val = DropDownList1.SelectedValue.ToString();
            GridView1.PageSize = Convert.ToInt16(val);
            BindGridview();
        }

The Output

Happy coding...


By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

Read More...

How to add dynamically rows number in jTable - Java

Most of the programming now a days required dynamically create controller such as button, textbox and also table. Today i want to show how to dynamically set rows number in jTable Swing Controls.

Firstly is to set the DefaultTableModel and assign to jTable.

Here is the example :

The DefaultTableModel

import javax.swing.table.DefaultTableModel; 

:
:

String colNames[] = {"Name", "Edit", "View"};  
DefaultTableModel dtm = new DefaultTableModel(null,colNames);  

Do looping to add rows in jTable

for(int i=0; i < listOfString .size();i++){
            dtm.addRow(new String[3]);

} 

Assign to jTable

 jTable2.setModel(dtm);

The full Code :

 String colNames[] = {"Name", "Edit", "View"}; 

 DefaultTableModel dtm = new DefaultTableModel(null,colNames);  

List<String> listOfString = new ArrayList<>();
        listOfString.add("1");
        listOfString.add("2");
        listOfString.add("3");
        listOfString.add("4");
        listOfString.add("5");
        listOfString.add("6");
        listOfString.add("7"); 

        
 for(int i=0; i < listOfString .size();i++){
       dtm.addRow(new String[3]);
}
        
 jTable2.setModel(dtm);

By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

Read More...

How to add button in JTable - Java

How to works with JTable in java and want to insert button in the JTable column.

Let say you want to add button in JTable column like in this pic :

 

 First step is to get the column of the button and set the CellRenderer for the column :

 jTable2.getColumn("Edit").setCellRenderer(new ButtonRenderer());
 jTable2.getColumn("Edit").setCellEditor(new ButtonEditor(new JCheckBox()));
            
 jTable2.getColumn("View").setCellRenderer(new ButtonRenderer());
 jTable2.getColumn("View").setCellEditor(new ButtonEditor(new JCheckBox()));

The Class ButtonRenderer

import java.awt.Component;
import javax.swing.JButton;
import javax.swing.JTable;
import javax.swing.UIManager;
import javax.swing.table.TableCellRenderer;

/**
 *
 * @author hp1
 */
public class ButtonRenderer extends JButton implements TableCellRenderer {

    public ButtonRenderer() {
        setOpaque(true);
    }

    @Override
    public Component getTableCellRendererComponent(JTable table, Object value,
            boolean isSelected, boolean hasFocus, int row, int column) {
        if (isSelected) {
            setForeground(table.getSelectionForeground());
            setBackground(table.getSelectionBackground());
        } else {
            setForeground(table.getForeground());
            setBackground(UIManager.getColor("Button.background"));
        }
        setText((value == null) ? "" : value.toString());
        return this;
    }
}

The Button Editor Class

import java.awt.Component;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.DefaultCellEditor;
import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JOptionPane;
import javax.swing.JTable;

/**
 *
 * @author hp1
 */
public class ButtonEditor extends DefaultCellEditor {

    protected JButton button;
    private String label;
    private boolean isPushed;

    public ButtonEditor(JCheckBox checkbox) {  
        super(checkbox);
        button = new JButton();
        button.setOpaque(true);
        button.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                fireEditingStopped();
            }
        });
    }

    @Override
    public Component getTableCellEditorComponent(JTable table, Object value,
            boolean isSelected, int row, int column) {
        if (isSelected) {
            button.setForeground(table.getSelectionForeground());
            button.setBackground(table.getSelectionBackground());
        } else {
            button.setForeground(table.getForeground());
            button.setBackground(table.getBackground());
        }
        label = (value == null) ? "" : value.toString();
        button.setText(label);
        isPushed = true;
        return button;
    }

    @Override
    public Object getCellEditorValue() {
        if (isPushed) {
            JOptionPane.showMessageDialog(button, label + ": Ouch!");
        }
        isPushed = false;
        return label;
    }

    @Override
    public boolean stopCellEditing() {
        isPushed = false;
        return super.stopCellEditing();
    }

    @Override
    protected void fireEditingStopped() {
        super.fireEditingStopped();
    }
}

By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

Read More...

Using SqlBulkCopy in c#

SqlBulkCopy is a powerful tools that allowed you to update/ insert/ or delete table in database with a batch update. SqlBulkCopy class offer you to load data source as long as the data can be loaded as DataTable or read with iDataReader . Using SqlBulkCopy you can perform :

• Single update / insert /  delete operation into database;
• Multiple update / insert /  delete operation into database;
• Bulk Copy operation with transaction. 

This tutorial only show single update / insert / delete operation using sql bulk copy .

The C# Method Code using SqlBulkCopy

        public void InsertUsingSqlBulkCopy(DataTable tableToUpdate, string tableName, ref string err)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Configuration.LocalDatabaseConnectionString))
            {
                bulkCopy.DestinationTableName = "dbo." + tableName;
                try
                {
                    // Write from the source to the destination.
                    bulkCopy.WriteToServer(tableToUpdate);
                }
                catch (SqlException sqlEx)
                {
                    err = sqlEx.Message;
              //static class to log the error
                    logging.writeLog(1, err, MethodBase.GetCurrentMethod().DeclaringType.Name);
                }
            }

        } 

By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)

Read More...