How to set icon to .exe file (C#.NET / VB.NET)

If you're using Visual Studio 2005, you can add the .ico/.png file in the C#
project through the Project Designer.

Detail Process:
1. Go to Solution Explorer and Right click the project item and choose Properties.
2. In the Project Designer, switch to the Application tab.
3. Select the option button 'Icon' in the Resources group and click the
button on the right to browse to your .ico file
4. After that save and build the project.
5. Now inside bin\debug u will get the project .exe file with your icon.

How to Programmatically Change the Caption of Windows Form using C#

If you would like to change the caption of the windows form during program running then write the following code.

      this.Text = "newForm";   
      
      Here on the right side what you write will display on the Form Caption.

Crystal Report in ASP.NET using C# with DataSet and DataTable

To create crystal report in ASP.NET dataSet and dataTable makes the programming so much easy.  In this article i have tried to show how DataSet ease our work and make the report creation flexible.

1. Add a DataSet to your Project and name it as myDataSet  as follows:

Click Yes. It will create a folder named App_Code in the solution window.
Now add a DataTable to myDataSet. Add columns to your DataTable. 
Your column name and datatype should be the same as that in your database.

Rename the DataTable name into "InfoTable"(without double quotes).

2. Add a Crystal report to the project and name it as myCrystalReport.

    Choose Using the report wizard.

    Choose the data source from project data which is myDataSet in our case.

Click (>) to move the table into 'Selected Tables' section

Choose the columns to be displayed on the report.

After that clicking finish will show the report which we can change the design at our choice.

Now let's see the code behind which will bind the data to the report.

SqlConnection connection = new SqlConnection("server='SERVER-NAME';uid= 'USER-ID'; "pwd='PASSWORD';database=DATABASE_NAME; Connect Timeout=1000000");
        connection.Open();  

        string selectQry = "YOUR-SQL-QUERY-HERE";        
        DataSet myDS = new DataSet();        
        SqlDataAdapter myDataAdapter = new SqlDataAdapter(selectQry, connection);
        myDataAdapter.Fill(myDS, "InfoTable");
        myReportDocument.Load(@Server.MapPath("myCrystalReport.rpt"));
        myReportDocument.SetDataSource(myDS);
        CrystalReportViewer1.ReportSource = myReportDocument;
        CrystalReportViewer1.DataBind();

Advantage

The main advantage of using DataSet is we can modify the table/Query and get effect from our DataSet. Suppose we may need to add a column in our report. Then we will modify our SQL Query in our program as well as add the new column to the myDataSet DataSet. After that open the Crystal Report and update it by as following way:

Open the Field Explorer -> Right Click on Database Fields -> Click Verify Database. A message will show that the DataSet has changed.

 

Clicking Ok will update the table field. After that we can use that/those field in our Crystal Report. 

Earlier when i use XML file to make report, that time to update the report is bit difficult. If i had a change in my SQL query then i had to build the XML file again, map to that XML into crystalReport and need to design Crystal Report again.

Using Data Set technique its now so much easy.

Keep coding......... and enjoy programming.............

How to upload Access(.mdb) table data to SQL Server with C#

I have a .mdb file inside which there are some tables. I want to upload one of the table's data into my SQL server's database table.

My SQL Server's database table name: Tab1

Access database table name: Customer

By the following C# code it is possible to transfer the data from access to sql server.

string str = "INSERT INTO Tab1(name,addr,NetworkID,UnitName) SELECT * FROM OPENROWSET";
str += "('Microsoft.Jet.OLEDB.4.0','E:\\Attn\\Upload_acss.mdb';'admin';'',Customer)";
SqlCommand sqlcom = new SqlCommand(str, connection);

   try
   {
        connection.Open();// connection is a SqlConnection type object which created before
        sqlcom.ExecuteNonQuery();
        connection.Close();
 

        Label1.ForeColor = System.Drawing.Color.Green;
        Label1.Text = "Inserted...";
   }
   catch (Exception ex) { Response.Write(ex.ToString()); }

Check to see if a table exists in SQL Server using C#

To do database related task we may sometime need to check if the sql table exists or not in the SQL Database. By the following code we can simply check this::

        string tblnm = 'your-table-name';
        string str = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" + tblnm + "'";
        SqlCommand myCommand = new SqlCommand(str, connection);
        SqlDataReader myReader = null;
        int count = 0;

        try
        {
            connection.Open();
            myReader = myCommand.ExecuteReader();
            while (myReader.Read())           
                count++;
           
            myReader.Close();
            connection.Close();
        }
        catch (Exception ex) { Response.Write(ex.ToString()); }
        if (count == 0)
            Label1.Text = "Table doesn't exists";
        else
            Label1.Text = "Table exists";

// by returning true/false based on count u can do your job also

We can check the table exists or not in another way.......

Suppose we want to count the row number of a table if that table exists. we can do so by following  way::

string tblnm = 'your-table-name';
string sql = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + tblnm + "]'))";
sql += " SELECT count(*) FROM " + tblnm;

connection.Open();
SqlCommand sqlcom = new SqlCommand(sql, connection);
SqlDataReader dr = sqlcom.ExecuteReader();
int c = -1;
while (dr.Read())
{
    c = (int)dr.GetValue(0);
}
connection.Close();
 
if (c != -1)
     Label2.Text = c.ToString()+" rows";
else
     Label2.Text = "table not exists";

Convert ArrayList String to String[ ] Array

Let we have a string[ ] array and a ArrayList of String. We would like to convert ArrayList to String[ ] .

static string[ ] userNameList;
ArrayList userNameAl = new ArrayList( );

userNameList = (string[ ])userNameAl.ToArray(typeof(string));

Upload Excel file to SQL Server from Local to Remote PC

Sometimes we may need to upload data files from user end to Server side. The following program will perform that task.

Here is the .aspx file code :

Here is the .cs file code :

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Collections;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

public partial class DataTransfer : System.Web.UI.Page
{

    protected HtmlInputFile myInputFile;
    SqlConnection connection = null;
    static string fileNameWithLocation = “”;

    protected void Page_Init(object sender, EventArgs e)
    {

       connection = new SqlConnection(“server=’SERVERNAME’;uid= ‘USERID’;” +    

        “pwd=’PASSWORD’;database=DATABASE″);
    }

    protected void Page_Load(object sender, EventArgs e)

    {
    }

    protected void upButton_Click(object sender, EventArgs e)
    {
        HttpPostedFile myFile = FileUpload1.PostedFile;
        if (myFile.FileName != “” && myFile.ContentLength > 0)
        {
           String ServerFileName = Path.GetFileName(myFile.FileName);
           fileNameWithLocation = “C:\\” + ServerFileName;
           myFile.SaveAs(fileNameWithLocation);

           SendDataToTable( );
           DeleteXLSFile( );
        }
        else
        {
           lblTransferMsg.ForeColor = System.Drawing.Color.Red;
           lblTransferMsg.Text = “Select a File !!!”;
        }
    }

    private void DeleteXLSFile( )
    {
       try
       {
          FileInfo theFile = new FileInfo(“C:\\a.xls”);
          if (theFile.Exists)
            File.Delete(“C:\\a.xls”);
          else
             throw new FileNotFoundException();
       }
       catch (FileNotFoundException fnfe)
       {
          Response.Write(fnfe.Message);
       }
       catch (Exception ex)
       {
           Response.Write(ex.Message);
       }
  }

  private void SendDataToTable( )
  {
     DeleteDataFromTable( );
     String strSQL = “”;
     strSQL = “Insert into ReportTable Select * FROM OPENROWSET”;
     strSQL += “(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=” + fileNameWithLocation + “;”;
     strSQL += “HDR = YES ‘,’SELECT * FROM [a$]‘)”;

    SqlCommand myCommand = new SqlCommand(strSQL, connection);
    try
    {
       connection.Open( );
       myCommand.ExecuteNonQuery( );
       connection.Close( );

       lblTransferMsg.ForeColor = System.Drawing.Color.Green;
       lblTransferMsg.Text = “Transfer success”;
    }
    catch (Exception ex) { Response.Write(ex.ToString()); }

 }

 private void DeleteDataFromTable( )
  {
    String strSQL = “”;
    strSQL = “DELETE FROM ReportTable”;
    SqlCommand myCommand = new SqlCommand(strSQL, connection);
    try
    {
       connection.Open( );
       myCommand.ExecuteNonQuery( );
       connection.Close( );
   }
   catch (Exception ex) { Response.Write(ex.ToString( )); }
}

//before uploading u have to create the table

Deleting a file using ASP.NET 2.0 and C# .NET

This tutorial will show you how to delete a file on the disk using ASP.NET 2.0 and C#.NET

To delete a simple file on the disk, we will need to first import the System.IO namespace. 

The System.IOFile.Delete() method and FileInfo type that we will use to perform our delete with.

using System.IO

We’ll put our code in the btnSubmit_Click() event.

When the btnSubmit_Click() event fires it first checks to see if the file exists using the FileInfo type. If it exists it runs the File.Delete() method to delete it, otherwise it throws a FileNotFoundException which is caught by one of the catch statements below the try block.

protected void btnSubmit_Click(object sender, EventArgs e)
{

  try {

      FileInfo TheFile = new FileInfo(MapPath(“.”) + “\\” + txtFile.Text);
      if (TheFile.Exists) {
        File.Delete(MapPath(“.”) + “\\” + txtFile.Text);
      }

     else {

       throw new FileNotFoundException();
      }

    }
    catch (FileNotFoundException ex) {

      lblStatus.Text += ex.Message;

    }
    catch (Exception ex) {

      lblStatus.Text += ex.Message;

    }

}

We have one textbox, a Submit button, a label, and a checkbox on the front end for user interaction. The front end .aspx page looks something like this:

 

The flow for the code behind page is as follows.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;

public partial class _Default : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {   } 

     protected void btnSubmit_Click(object sender, EventArgs e)
    {

      try {

        FileInfo TheFile = new FileInfo(MapPath(“.”) + “\\” + txtFile.Text);
        if (TheFile.Exists) {
          File.Delete(MapPath(“.”) + “\\” + txtFile.Text);
        }

        else {

           throw new FileNotFoundException();
        }

      }catch (FileNotFoundException ex) {

         lblStatus.Text += ex.Message;

      }
      catch (Exception ex) {

          lblStatus.Text += ex.Message;

      }

}

Get Connection string from web.config in ASP.NET 2.0, 3.5

In Asp.Net 2.0 and Asp.Net 3.5 we have a Tag Inside Web.config file that is ConnectionString Tag.

<connectionStrings>
<add name=”MyConnString” connectionString=”Server=ServerName;Database=DBName;
Uid=UserId;Pwd=Passwd;” providerName=”System.Data.SqlClient“/>
connectionStrings>

Now to get the Connection string defined in the web.config file you have to do like this in your .cs  file. Below code is based on C#

string Conn= ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;


In Vb.NET
Dim Conn as String = ConfigurationManager.ConnectionStrings(“MyConnString“).ConnectionString

Getting datetime for first day In a Month

protected void Page_Load(object sender, EventArgs e)
{

   DateTime dtRet = GetFirstInMonth(GetDateValue("2010-03-02"));
   Label1.Text = dtRet.ToShortDateString();

}
 

public static DateTime GetFirstInMonth(DateTime dt)
 {
        DateTime dtRet = new DateTime(dt.Year, dt.Month, 1, 0, 0, 0);
        return dtRet;
}
 
Output:
3/1/2010

Convert string to datetime

This is a favorite function I used a lot when getting data from remote systems. For example I once needed to retrieve data from an AS/400 box, and the file format was already specified. However, date fields in the file could look pretty different (6, 8, 10 digits with or without delimiters between year/month/day), depending on certain aspects so my timesaving all-purpose reoutine for getting date values look like this: 

 public static DateTime GetDateValue(string sVal)

    {

        DateTime dtRet;

        try

        {

            int nAdd = 1900;

            if (Convert.ToInt32(sVal.Substring(0, 2)) < 80)

                nAdd = 2000;

            if (sVal.Length == 6)

            {

                //YYMMDD

                dtRet = new DateTime(nAdd + Convert.ToInt32(sVal.Substring(0, 2)), Convert.ToInt32(sVal.Substring(2, 2)), Convert.ToInt32(sVal.Substring(4, 2)), 0, 0, 0);

                return dtRet;

            }

            if (sVal.Length == 8)

            {

                if (sVal.IndexOf("-") > 0)

                {

                    //YY-MM-DD

                    dtRet = new DateTime(nAdd + Convert.ToInt32(sVal.Substring(0, 2)), Convert.ToInt32(sVal.Substring(3, 2)), Convert.ToInt32(sVal.Substring(6, 2)), 0, 0, 0);

                    return dtRet;

                }

                //YYYYMMDD

                dtRet = new DateTime(Convert.ToInt32(sVal.Substring(0, 4)), Convert.ToInt32(sVal.Substring(4, 2)), Convert.ToInt32(sVal.Substring(6, 2)), 0, 0, 0);

                return dtRet;

            }

            if (sVal.Length == 10)

            {

                //YYYY-MM-DD

                dtRet = new DateTime(Convert.ToInt32(sVal.Substring(0, 4)), Convert.ToInt32(sVal.Substring(5, 2)), Convert.ToInt32(sVal.Substring(8, 2)), 0, 0, 0);

                return dtRet;

            }

            return Convert.ToDateTime(sVal);

        }

        catch (Exception){}

             
             dtRet = new DateTime(1900, 01, 01, 0, 0, 0);

        return dtRet;

    }

protected void Page_Load(object sender, EventArgs e)
    {       
        textBox1.Text += "010102->" + GetDateValue("010102").ToString("yyyy-MM-dd") + Environment.NewLine;
        textBox1.Text += "790102->" + GetDateValue("790102").ToString("yyyy-MM-dd") + Environment.NewLine;
        textBox1.Text += "800102->" + GetDateValue("800102").ToString("yyyy-MM-dd") + Environment.NewLine;
        textBox1.Text += "810102->" + GetDateValue("810102").ToString("yyyy-MM-dd") + Environment.NewLine;
        textBox1.Text += "20000102->" + GetDateValue("20000102").ToString("yyyy-MM-dd") + Environment.NewLine;
        textBox1.Text += "01-01-02->" + GetDateValue("01-01-02").ToString("yyyy-MM-dd") + Environment.NewLine;
        textBox1.Text += "79-01-02->" + GetDateValue("79-01-02").ToString("yyyy-MM-dd") + Environment.NewLine;
        textBox1.Text += "80-01-02->" + GetDateValue("80-01-02").ToString("yyyy-MM-dd") + Environment.NewLine;
        textBox1.Text += "810102->" + GetDateValue("81-01-02").ToString("yyyy-MM-dd") + Environment.NewLine;
        textBox1.Text += "2000-01-02->" + GetDateValue("2000-01-02").ToString("yyyy-MM-dd") + Environment.NewLine;
       
    }

Here is the .aspx file ::

Output::

010102->2001-01-02
790102->2079-01-02
800102->1980-01-02
810102->1981-01-02
20000102->2000-01-02
01-01-02->2001-01-02
79-01-02->2079-01-02
80-01-02->1980-01-02
810102->1981-01-02
2000-01-02->2000-01-02