Distinct count in MS SQL

The below code will retrieve distinct count from a table in SQL

select ename, count(ename) from emp

Group by ename having (count(ename)>1)

Inserting Data rows with Parameters and Stored procedure in C# and MS SQL Server

In C# you can do your SQL things in many ways, one of the safest and fastest method in execute SQL commands from SQL server itself. Suppose you want to new row to Product table,

1. First you need to setup SqlDataAdapter
2. Create Parameter Objects
3. Create SQL Procedure with Parameters

Lets do one by one

SqlDataAdapter

con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings
["MACCon"].ConnectionString;
 con.Open();
 ProductTableAdapter = new SqlDataAdapter("select * from productMaster", con);

We are now accessed the Product table with SqlDataAdapter and ready to create Parameter sets.

Setting up Parameters with values

ProductTableAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
 ProductTableAdapter.SelectCommand.CommandText = "CRUDS_PRODUCTMASTER";
 ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@Item", cmb_item.Text.ToString().ToUpper()));
 ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@mfr", cmb_mfr.Text.ToString().ToUpper())); Common.ProductTableAdapter.SelectCommand.ExecuteScalar();

with the SqlParameter  we can pass values to the SQL Procedure that we are going to create in next step. The commandType of stored procedure indicate the it is StoredProcedure. Now all we need is a Procedure.

Design your  SQL Stored Procedure

Open you SQL Server Explorer or SQL Server Itself and expand Procedure node and drop the following line of code.

ALTER PROCEDURE dbo.CRUDS_PRODUCTMASTER
 (
 @item nchar(10),
 @mfr nchar(10),
 )
 AS
 Begin
 insert into productmaster  (item,mfr) values(@item,@mfr,);
 End

The parameters in C# and SQL should match otherwise it will cause errors.

Truncate a database with sp_MSforeachtabl in MS SQL Server

There may be occasion when you need to clear all data in MS SQL Database (the most sophisticated and user friendly RDBMS available today). Here is the quick guide.

Truncate with sp_MSforeachtable Stored Procedure

Access you MS SQL Server Studio or CTP. As we all know this can be get done the job with TRUNCATE command which delete data and release physical memory too. You have to perform the command for each table in database. But the MS SQL provide a better and easy way to do the task with a stored procedure.

Start a new SQL Query and add the following command to delete all data from tables.

USE MyDatabaseEXEC sp_MSforeachtable ‘TRUNCATE TABLE ?’

This will clear all data from table who has no foreign keys applied. You can alternatively use the following command which will only delete the contents and not the memory things.

USE MyDatabaseEXEC sp_MSforeachtable ‘DELETE FROM ?’