I need a validation before Insert data in database, when i upload excel file it check excel column usercode data match with database usercode Column.If true then go to InsertData(); if False return a error, Usercode is not exist, Below i Mention th code,
ALTER proc [dbo].[Check]
(
@UserCode varchar(50),
@statCode int OUTPUT
)
AS
if not exists (select UserCode from Employee where UserCode=@UserCode )
BEGIN
SET @statCode
END
private void CheckData()
{
String path = Server.MapPath("~/ExcelFile/");
String fullPath = path + FileUpload.FileName;
string connString = "";
string strFileType = Path.GetExtension(FileUpload.FileName).ToLower();
FileUpload.PostedFile.SaveAs(fullPath);
string Excel = FileUpload.PostedFile.FileName;
//Connection String to Excel Workbook
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;Persist Security Info = False;IMEX=2\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullPath + ";Extended Properties=\"Excel 12.0;HDR=Yes; Persist Security Info = False;IMEX=2\"";
}
OleDbConnection conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed)
conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string UserCode1 = dr[0].ToString();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["abc"].ConnectionString);
con.Open();
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = con;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "check";
cmd2.Parameters.AddWithValue("@UserCode", UserCode1);
SqlParameter OutputParam = cmd2.Parameters.Add("@statCode", SqlDbType.Int);
OutputParam.Direction = ParameterDirection.Output;
int statCode = Convert.ToInt32(cmd2.Parameters["@statCode"].Value.ToString());
cmd2.ExecuteNonQuery();
con.Close();
Response.Write(statCode);
}
conn.Close();
conn.Dispose();
File.Delete(fullPath);
}
protected void Import_Click(object sender, EventArgs e)
{
CheckData();
InsertData();
BindGrid();
}