Import excel data into SQL Server table using ASP.NET

string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";

using (OleDbConnection connection = new OleDbConnection(xConnStr))
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString =DataAccess.GetConnectionString() ;

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "dbo.ExcelTest";
bulkCopy.WriteToServer(dr);
}
}
}

Insert data into multiple tables-Dynamic Query

If you have multiple related tables and need to insert data on those tables. You can write stored procedures or the dynamic query. I am going to explain here the dynamic query.

First, insert the data into primary table and read the auto generated ID from that table and insert other data and that ID where it is foreign key. Each different query should be separated by semicolon(;).

To find ID of the row when data is inserted on a table, run a sql command " scope_identity()" right after insert the data.

Hence the Dynamic query is:

string sql = " DECLARE @ID int;@ID1 int; @ID3 int; @ID4 int;@ID5 int;";
sql = sql + "Insert into table1(Url,head,foot) values ";
sql = sql + "(@url,@head,@foot);";
sql = sql + " SET @ID = SCOPE_IDENTITY();";
sql = sql + " Insert into table2(table2_Name) values(@table2Name);";
sql = sql + " SET @ID1 = SCOPE_IDENTITY();";
sql = sql + " Insert into table3(tabl3_Name) values(@table3Name);";
sql = sql + " SET @ID2 = SCOPE_IDENTITY();";
sql = sql + " Insert into table2_table3(table1_ID,table2_ID) values (@ID1,@ID2);";
sql = sql + " SET @ID3 = SCOPE_IDENTITY();";
sql = sql + " INSERT INTO dbo.table1_table2(table1_ID,table2_table3_ID) VALUES (@ID,@ID3);";

SqlConnection conn = new SqlConnection(GetConnectionString());
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;

IDbDataParameter userParam = cmd.CreateParameter();
userParam.ParameterName = "@url";
userParam.Value = formReserve.URL;
userParam.DbType = System.Data.DbType.String;
cmd.Parameters.Add(userParam);
IDbDataParameter userParam = cmd.CreateParameter();

......

.....

.....

cmd.ExecuteNonQuery();

}

The GridView 'GridView1' fired event Sorting which wasn't handled

If you got this error message when sorting a Gridview:
The GridView 'GridView1' fired event sorting which wasn't handled.

Then do the following add/changes in your code behind.

Aspx.cs Page:
public SortDirection GridViewSortDirection
{
get
{
if (ViewState["sortDirection"] == null)
ViewState["sortDirection"] = SortDirection.Ascending;
return (SortDirection)ViewState["sortDirection"];
}
set { ViewState["sortDirection"] = value; }
}

protected void Gridview1_Sorting(object sender, GridViewSortEventArgs e)

{
string strSortDir = null;switch (GridViewSortDirection)
{
case SortDirection.Ascending:GridViewSortDirection = SortDirection.Descending;
strSortDir = "DESC";
break;
case SortDirection.Descending:GridViewSortDirection = SortDirection.Ascending;
strSortDir = "ASC";
break;
}
SqlDataSource1.SelectCommand = "SELECT ...... FROM tableName order by " + e.SortExpression + " " + strSortDir;
}

More: http://forums.asp.net/t/1286994.aspx

Parameterized SQL Query in sqldatasource and bind with Gridview

To avoid SQL injections you have to write stored procedure or the parametrized query. If you have a sqldatasource, want to pass the paramterized query to that datasource and finnally display with Gridview...Just customize your code like follow:

string sql = "SELECT Guid as Guid,[ID], [Family Name] AS Family_Name, [Given Name] AS Given_Name, [Region], [MacroRegion], [Country], [Years], [Date] FROM [Author] where [Given Name] LIKE '%' + @Given_Name + '%'";

SqlDataSource1.SelectCommand = sql;
SqlDataSource1.SelectParameters.Clear();
SqlDataSource1.SelectParameters.Add("Given_Name", TypeCode.String, searchTextBox.Text.Trim());
GridView1.DataSource = SqlDataSource1;
GridView1.DataSourceID = string.Empty;
GridView1.DataBind();

More:http://forums.asp.net/t/1309562.aspx