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