NOT save value on PostBack

If you have different ASP.NET controls in your application e.g checkbox, listbox, textbox, dropdowns etc. and you want to clear the value that it holds on PostBack. Then do the

EnableViewStateProperty to False.

or you can do

Textbox1.Text="";

after submitting form data.

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();

}