C#.NET - Custom DateTime formats with examples

using System;

class Program

{

    static void Main(string[] args)

    {

        string str = string.Empty;

        //There are following custom format specifiers

        //y Year

        str = String.Format("{0:y yy yyy yyyy}", DateTime.Now);

        //13 13 2013 2013

        //M Month

        str = String.Format("{0:M MM MMM MMMM}", DateTime.Now);

        //10 10 Oct October

        //d Day

        str = String.Format("{0:d dd ddd dddd}", DateTime.Now);

        //23 23 Wed Wednesday

        //h 12-Hours, H 24-Hours

        str = String.Format("{0:h hh H HH}", DateTime.Now);

        //3 03 15 15

        //m Minute

        str = String.Format("{0:m mm}", DateTime.Now);

        //0 00

        //s Second

        str = String.Format("{0:s ss}", DateTime.Now);

        //22 22

        //f Second Fraction

        str = String.Format("{0:f ff fff ffff}", DateTime.Now);

        //4 47 473 4737

        //t A.M or P.M

        str = String.Format("{0:t tt}", DateTime.Now);

        //P PM

        //z Time Zone

        str = String.Format("{0:z zz zzz}", DateTime.Now);

        //+5 +05 +05:30

    }

}

How to create dynamic DataTable in C#.NET?

using System;

using System.Data;

class Program

{

    static void Main(string[] args)

    {

        DataTable table = GetTable();

    }

    static DataTable GetTable()

    {

        //Create new DataTable instance

        DataTable table = new DataTable("Indian Cricket Team");

        // Add Columns to table

        table.Columns.Add("Name", typeof(string));

        table.Columns.Add("Age", typeof(int));

        table.Columns.Add("Batting style", typeof(string));

        table.Columns.Add("Bowling style", typeof(string));

        //Add Rows to Table

        table.Rows.Add("Mahendra Singh Dhoni", 32, "Right-handed", "Right medium");

        table.Rows.Add("Virat Kohli", 24, "Right-handed", "Right medium");

        table.Rows.Add("Virender Sehwag", 34, "Right-handed", "Off break");

        table.Rows.Add("Suresh Raina", 26, "Left-handed", "Off break");

        //.............

        //.............

        //Return the DataTable

        return table;

    }

}

C#.NET - How to use OUTPUT clause in Stored Procedures to retrieve new identity values from SQL Server

SQL Server 2005 introduced the OUTPUT clause in DML statements.
We can use this in an INSERT ... SELECT statement to capture all the inserted identity values.

1. Create an Employee table

 CREATE TABLE [dbo].[tbl_employee]
  (
     [serial]      [INT] IDENTITY(1, 1) NOT NULL,
     [empname]     [VARCHAR](50) NULL,
     [empsal]      [VARCHAR](50) NULL,
     [emplocation] [VARCHAR](50) NULL
  )     

2. Create a stored procedure to get the identity value

 CREATE PROCEDURE [dbo].[Temp]
 AS
  BEGIN
      -- Declare table variable to hold results
      DECLARE @Temp TABLE
        (
           serial INT
        )

      -- Insert into table with IDENTITY column      
      INSERT INTO [Temp].[dbo].[tbl_employee]
                  ([empname],
                   [empsal],
                   [emplocation])
      -- Use OUTPUT clause to retrieve new value 
      output      inserted.serial
      INTO @Temp(serial)
      VALUES      ('emp1',
                   '15000',
                   'Hyderabad')

      --Select the identity value
      SELECT serial
      FROM   @Temp
  END     

3. Open web.config file and set connection string as follows

<connectionStrings>
   <add name="SiteSqlServer" connectionString="Data Source=ADMIN-PC;Initial Catalog=Temp;User ID=sa;Password=sql@121"
      providerName="System.Data.SqlClient" />
</connectionStrings>

4. Open code behind file and add following namespaces

using System.Data.SqlClient;
using System.Collections.Generic;

5. Write the following method to retrieve data from Stored Procedure.

public static object UsingTheOutputClauseExecuteCommandAndRetriveNewIdentity(string storedProcedureName, List<SqlParameter> sqlParameters)
    {
        string outputParameterValue = string.Empty;
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        SqlCommand command = new SqlCommand(storedProcedureName, connection);
        command.CommandType = CommandType.StoredProcedure;
        if (sqlParameters.Count > 0)
        command.Parameters.AddRange(sqlParameters.ToArray());        
        using (connection)
        {
            connection.Open();
            object num = command.ExecuteScalar();           
            connection.Close();
        }
        return outputParameterValue;
    }
    

6. Finally use the following code to retrieve new identity values from SQL Server.

List<SqlParameter> sqlparams = new List<SqlParameter>();
object str = UsingTheOutputClauseExecuteCommandAndRetriveNewIdentity("Temp",sqlparams);  

Stored Procedures - How to use OUTPUT clause to retrieve new identity values from SQL Server 2008

SQL Server 2005 introduced the OUTPUT clause in DML statements.
We can use this in an INSERT ... SELECT statement to capture all the inserted identity values.

Create an Employee table

CREATE TABLE [dbo].[tbl_employee]
  (
     [serial]      [INT] IDENTITY(1, 1) NOT NULL,
     [empname]     [VARCHAR](50) NULL,
     [empsal]      [VARCHAR](50) NULL,
     [emplocation] [VARCHAR](50) NULL
  ) 

Create a stored procedure to get the identity value

CREATE PROCEDURE [dbo].[Temp]
AS
  BEGIN
      -- Declare table variable to hold results
      DECLARE @Temp TABLE
        (
           serial INT
        )

      -- Insert into table with IDENTITY column      
      INSERT INTO [Temp].[dbo].[tbl_employee]
                  ([empname],
                   [empsal],
                   [emplocation]) 

      -- Use OUTPUT clause to retrieve new value 
      output      inserted.serial
      INTO @Temp(serial)
      VALUES      ('emp1',
                   '15000',
                   'Hyderabad')

      --Select the identity value
      SELECT serial
      FROM   @Temp
  END  

If you execute the above stored procedure you will get the identity of the last inserted row.

EXEC [dbo].[Temp]  

Serial
------------------------------------------------------------
  1

EXEC [dbo].[Temp]  

Serial
------------------------------------------------------------
  2

EXEC [dbo].[Temp]  

Serial
------------------------------------------------------------
  3

How to check string is number or not in C#

Method

public static Boolean IsNumeric(string stringToTest)
{
    int result;
    return int.TryParse(stringToTest, out result);
}

Usage

bool result = IsNumeric("value"); //result is false
bool result = IsNumeric("1234"); //result is true

No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type.

How to Resolve No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type error in C#.NET?

You will get this type of problems when you pass Direct control ( TextBox ) as SQLParameter value.

EX:

 protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = WebConfigurationManager.AppSettings["ConnectionString"];
        SqlCommand cmd=new SqlCommand("insert into register values(@name,@address,@password,@confirmpassword)",con);  
        cmd.Parameters.AddWithValue("@name",TextBox1);
        cmd.Parameters.AddWithValue("@address", TextBox2);
        cmd.Parameters.AddWithValue("@password", TextBox3);
        cmd.Parameters.AddWithValue("@confirmpassword", TextBox4);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

If you observe the above code you are passing the TextBoxes as SQLParameter values.

You should pass the values of the Controls to SQLParameter as follows. 

 protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = WebConfigurationManager.AppSettings["ConnectionString"];
        SqlCommand cmd=new SqlCommand("insert into register values(@name,@address,@password,@confirmpassword)",con);  
        cmd.Parameters.AddWithValue("@name",TextBox1.Text);
        cmd.Parameters.AddWithValue("@address", TextBox2.Text);
        cmd.Parameters.AddWithValue("@password", TextBox3.Text);
        cmd.Parameters.AddWithValue("@confirmpassword", TextBox4.Text);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

Simple method in C#.NET to Select Count from SQL Server using SqlCommand.ExecuteScalar() method and with SqlParameters.

Here i implemented a simple method to Select Count value from SQL Server. I am implementing this method by using SqlCommand.ExecuteScalar() method and SqlParameter's. This method is better is secure way also.

Namespaces:

using System.Data.SqlClient;
using System.Collections.Generic;

Method: 

public static int PerformSQLSelectCountOperation(string sqlQuery, List<SqlParameter> sqlParameters, string sqlConnectionString)
    {
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        SqlCommand selectCommand = new SqlCommand(sqlQuery, connection);
        if (sqlParameters.Count > 0)
            selectCommand.Parameters.AddRange(sqlParameters.ToArray());
        connection.Open();
        int num = Convert.ToInt32(selectCommand.ExecuteScalar());
        connection.Close();
        return num;
    }

How can we use this method?

string sqlConnectionString = "Data Source=ADMIN-PC;Initial Catalog=dvtech;User ID=sa;Password=sql@121";
string sqlQuery = "Select count(*) from employee where Location=@Location";
List<SqlParameter> sqlParams = new List<SqlParameter>();
sqlParams.Add(new SqlParameter("@Location", "Hyderabad"));
int result = PerformSQLSelectCountOperation(sqlQuery, sqlParams, sqlConnectionString);

Simple method in C#.NET to Select Single cell value from SQL Server using SqlCommand.ExecuteScalar() method and SqlParameters.

Here i implemented a simple method to Select single cell value from SQL Server. I am implementing this method by using SqlCommand.ExecuteScalar() method and SqlParameter's. This method is better is secure way also.

Namespaces:

using System.Data.SqlClient;
using System.Collections.Generic;

Method: 

public static string PerformSQLSelectSingleCellOperation(string sqlQuery, List<SqlParameter> sqlParameters, string sqlConnectionString)
    {
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        SqlCommand selectCommand = new SqlCommand(sqlQuery, connection);
        if (sqlParameters.Count > 0)
            selectCommand.Parameters.AddRange(sqlParameters.ToArray());
        connection.Open();
        string str = Convert.ToString(selectCommand.ExecuteScalar());
        connection.Close();
        if (!string.IsNullOrEmpty(str))
            return str;
        else
            return string.Empty;
    }

How can we use this method?

string sqlConnectionString = "Data Source=ADMIN-PC;Initial Catalog=dvtech;User ID=sa;Password=sql@121";
string sqlQuery = "Select empname from employee where Location=@Location";
List<SqlParameter> sqlParams = new List<SqlParameter>();
sqlParams.Add(new SqlParameter("@Location", "Hyderabad"));
string result = PerformSQLSelectSingleCellOperation(sqlQuery, sqlParams, sqlConnectionString);

Simple method in C#.NET to Select data from SQL Server using SqlDataAdapter.Fill() method and SqlParameters.

Here i implemented a simple method to perform the Select operation in SQL Server. I am implementing this method by using SqlDataAdapter.Fill() method and SqlParameter's. This method is better is secure way also.

Namespaces:

using System.Data.SqlClient;
using System.Collections.Generic;

Method: 

public static DataTable PerformSQLSelectOperation(string sqlQuery, List<SqlParameter> sqlParameters)
    {
        DataTable dataTable = new DataTable();
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        SqlCommand selectCommand = new SqlCommand(sqlQuery, connection);
        if (sqlParameters.Count > 0)
            selectCommand.Parameters.AddRange(sqlParameters.ToArray());
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand);
        sqlDataAdapter.SelectCommand = selectCommand;
        sqlDataAdapter.Fill(dataTable);
        return dataTable;
    }

How can we use this method?

string sqlConnectionString = "Data Source=ADMIN-PC;Initial Catalog=dvtech;User ID=sa;Password=sql@121";
string sqlQuery = "Select * from employee where Location=@Location";
List<SqlParameter> sqlParams = new List<SqlParameter>();
sqlParams.Add(new SqlParameter("@Location", "Hyderabad"));
DataTable noOfEffectedRows = PerformSQLSelectOperation(sqlQuery, sqlParams, sqlConnectionString);

Simple method in C#.NET to Insert Update or Delete data in SQL Server using SqlCommand.ExecuteNonQuery() method and SqlParameters.

Here i implemented a simple method to perform the Insert Update of Delete actions in SQL Server. I am implementing this method by using SqlCommand.ExecuteNonQuery() method and SqlParameter's. This method is better is secure way also.

Namespaces:

using System.Data.SqlClient;
using System.Collections.Generic;

Method: 

public static int PerformSQLInsertOrUpdateOrDeleteOperation(string sqlQuery, List<SqlParameter> sqlParameters, string sqlConnectionString)
    {
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        SqlCommand selectCommand = new SqlCommand(sqlQuery, connection);
        if (sqlParameters.Count > 0)
            selectCommand.Parameters.AddRange(sqlParameters.ToArray());
        using (connection)
        {
            connection.Open();
            int num = selectCommand.ExecuteNonQuery();
            connection.Close();
            return num;
        }
    }

How can we use this method?

string sqlConnectionString = "Data Source=ADMIN-PC;Initial Catalog=dvtech;User ID=sa;Password=sql@121";
string sqlQuery = "Select * from employee where Location=@Location";
List<SqlParameter> sqlParams = new List<SqlParameter>();
sqlParams.Add(new SqlParameter("@Location", "Hyderabad"));
int noOfEffectedRows = PerformSQLInsertOrUpdateOrDeleteOperation(sqlQuery, sqlParams, sqlConnectionString);