export excel data into sql server using c#

Condition : excel file columns and sql server tables columns should be same and datatype also should be same.

public void Exel2Sql(){OdbcConnection connection;SqlBulkCopy bulkCopy;string ConnectionString = @”server=sujitkumar\sqlexpress;database=pubs;uid=sa;pwd=1234;”;string connstr = @”Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\contact.xls”;using (connection = new OdbcConnection(connstr)){OdbcCommand command = new OdbcCommand(”Select * FROM [Sheet1$]“, connection);
//you can change [Sheet1$] with your sheet name
connection.Open();
// Create DbDataReader to Data Worksheet
using (OdbcDataReader dr = command.ExecuteReader()){// Bulk Copy to SQL Server
using (bulkCopy = new SqlBulkCopy(ConnectionString)){bulkCopy.DestinationTableName = “Names”;//”Names” is the sql table where you want to copy all data.
bulkCopy.WriteToServer(dr);}dr.Close();}
}
bulkCopy.Close();connection.Close();}

Show Image from SQL Server in ASP.NET Using C#

SqlConnection objConnection;SqlCommand objCommand;SqlDataAdapter objAdapter;DataSet objDS=new DataSet();
using (objConnection = new SqlConnection(”server=sujitkumar\\sqlexpress;database=livechat;uid=sa;pwd=1234″)){objConnection.Open();using (objCommand = new SqlCommand()){objCommand.Connection = objConnection;
objCommand.CommandType = CommandType.Text;objCommand.CommandText = “select img_online from image_master”;
objAdapter = new SqlDataAdapter(objCommand);objAdapter.Fill(objDS, “CustomerAttributes”);}objConnection.Close();}
MemoryStream stream = new MemoryStream();byte[] image = (byte[])objDS.Tables[0].Rows[0][0];Response.ContentType = “image/jpeg”;Response.Expires = 0; Response.Buffer =true;Response.Clear();Response.BinaryWrite(image);Response.End();


The above code will write image on the page.
You can show this image in any image button or image control like:

A potentially dangerous Request.Form value

This error occurs when we try to post html code from code behind.The reason behind it server always validate the posted data, if it find that its an HTML code, error will occues like ‘A potentially dangerous Request.Form value…’.

if you want to send html data then you hvae to write {validateRequest=”false”} in page directiveex:
AutoEventWireup=”true” ValidateRequest=”false”

Filter table at runtime from dataset and store it in datatable in c#

In this article I am filtering data from dataset and store it in different different table.

//connect to sqlcon = new SqlConnection(”Data Source=firoz\\sqlexpress;Initial Catalog=pubs;Integrated Security=SSPI”);
//Here I have filled up dataset with two table, in first table all records are comming and in second table only unique id are coming from database.

String query = “select * from student;select distinct(id) from student”;
da = new SqlDataAdapter(query, con);da.Fill(ds);
//here I filled up different different table with unique id.
int count = ds.Tables[1].Rows.Count-1;
for (int i = 0; i <= count; i++){DataView dv = ds.Tables[0].DefaultView;
dv.RowFilter = “id=” + ds.Tables[1].Rows[i][0];
DataTable dt = new DataTable();dt=dv.ToTable(i.ToString());ds.Tables.Add(dt);}

Reset or Reseed Identity on tables with identity column in sql server

Run this command to reset or reseed Identity columns of a table.

exec sp_MSforeachtable‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1BEGIN DBCC CHECKIDENT (”?”,RESEED,0) END’
Once you run this query on a table it will reset Identity column always.

How to get Disticnt rows/value from a DataSet or Datatable

Following line will give you distict result from dataset/datatable .

DataSet ds=new DataSet();
//code to fill dataset from database.
DataTable dt=new DataTable();
dt=ds.Tables["0"].DefaultView.ToTable(true,”columnName”);
//here true means you want distict result.

Get comma separated column vaue in SQL

CREATE PROCEDURE [dbo].[EmailIds] AS Declare @Email VARCHAR(max) Select @Email = COALESCE(@Email + ', ', '') + email from usersnew Select @Email as Email