C SHARP .NET

(Home)

Connection String

myDataGrid.CurrentPageIndex = e.NewPageIndex;

//above line must execute first!

SqlConnection conn = new SqlConnection("User ID=xxxxx;password=   xxxxx;Initial Catalog=xxxxx;Server=xxxxx; integrated security = sspi");

SqlCommand comm = new SqlCommand("SELECT wo_id as [Work Order], wo_date as Date, wo_facility_engr as [Facility Engineer] FROM tblWorkOrder_hdr ORDER BY wo_id DESC", conn);

SqlDataAdapter da = new SqlDataAdapter(comm);

DataSet ds = new DataSet();

da.Fill(ds,"tblWorkOrder_hdr");

myDataGrid.DataSource=ds.Tables["tblWorkOrder_hdr"].DefaultView;

myDataGrid.DataBind();

SqlCommand cmdWorkOrderAdd1 = new SqlCommand("spWorkOrder_Insert", conn);

SqlCommand cmdWorkOrderAdd2 = new SqlCommand("spWorkOrder_Insert_mgr", conn);

cmdWorkOrderAdd1.CommandType = CommandType.StoredProcedure;

cmdWorkOrderAdd2.CommandType = CommandType.StoredProcedure;

//Fill ClsRequest

//req.reqStatus = 0 by default into db record!

clsRequest req = new clsRequest();

req.reqAdministrator = Convert.ToString(LoginData.getUserid().Userid);

req.reqDate = System.DateTime.Now;

req.reqAdminSite = LoginData.getSiteID().Site;

req.reqAdminDept = LoginData.getDeptID().DeptID;

req.reqSite = Convert.ToInt32(wksite.SelectedValue);

req.reqDept = Convert.ToString(wkDept.SelectedValue);

req.reqRequest = Convert.ToString(wkDescription.Text);

req.reqPriority = Convert.ToInt32(wkPriority.SelectedValue);

req.reqCategory = Convert.ToInt32(wkCategory.SelectedValue);

//Fill Parameters

//Request Date

SqlParameter parm1=new SqlParameter("@worReqDate",SqlDbType.DateTime) ;

parm1.Value=Convert.ToDateTime(req.reqDate);

parm1.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd1.Parameters.Add(parm1);

//Administrator

SqlParameter parm2=new SqlParameter("@worAdministrator",SqlDbType.NVarChar) ;

parm2.Value=Convert.ToString(req.reqAdministrator);

parm2.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd1.Parameters.Add(parm2);

//Administrator Site

SqlParameter parm3=new SqlParameter("@worAdminSite",SqlDbType.Int) ;

parm3.Value=Convert.ToInt32(req.reqAdminSite);

parm3.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd1.Parameters.Add(parm3);

//Administrator Dept

SqlParameter parm4=new SqlParameter("@worAdminDept",SqlDbType.NVarChar) ;

parm4.Value=Convert.ToString(req.reqAdminDept);

parm4.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd1.Parameters.Add(parm4);

//Work Site

SqlParameter parm5=new SqlParameter("@worSite",SqlDbType.Int) ;

parm5.Value=Convert.ToInt32(req.reqSite);

parm5.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd1.Parameters.Add(parm5);

//Work Dept

SqlParameter parm6=new SqlParameter("@worDept",SqlDbType.NVarChar) ;

parm6.Value=Convert.ToString(req.reqDept);

parm6.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd1.Parameters.Add(parm6);

//worRequest

SqlParameter parm7=new SqlParameter("@worRequest",SqlDbType.NText) ;

parm7.Value=Convert.ToString(req.reqRequest);

parm7.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd1.Parameters.Add(parm7);

//worPriority

SqlParameter parm8=new SqlParameter("@worPriority",SqlDbType.Int) ;

parm8.Value=Convert.ToInt32(req.reqPriority);

parm8.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd1.Parameters.Add(parm8);

//worCategory

SqlParameter parm9=new SqlParameter("@worCategory",SqlDbType.Int) ;

parm9.Value=Convert.ToInt32(req.reqCategory);

parm9.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd1.Parameters.Add(parm9);

//newID - RETURN Value

SqlParameter parm10=new SqlParameter("@newID",SqlDbType.Int);

parm10.Direction=ParameterDirection.Output;

cmdWorkOrderAdd1.Parameters.Add(parm10);

//open and the close connection around the execution

conn.Open();

//Execute StoredProcedur

cmdWorkOrderAdd1.ExecuteNonQuery();

//Request Date

SqlParameter param0=new SqlParameter("@worReqID",SqlDbType.Int) ;

param0.Value=Convert.ToInt32(cmdWorkOrderAdd1.Parameters["@newID"].Value.ToString());

param0.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd2.Parameters.Add(param0);

//Request Date

SqlParameter param1=new SqlParameter("@worReqDate",SqlDbType.DateTime) ;

param1.Value=Convert.ToDateTime(req.reqDate);

param1.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd2.Parameters.Add(param1);

//Administrator

SqlParameter param2=new SqlParameter("@worAdministrator",SqlDbType.NVarChar) ;

param2.Value=Convert.ToString(req.reqAdministrator);

param2.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd2.Parameters.Add(param2);

//Administrator Site

SqlParameter param3=new SqlParameter("@worAdminSite",SqlDbType.Int) ;

param3.Value=Convert.ToInt32(req.reqAdminSite);

param3.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd2.Parameters.Add(param3);

//Administrator Dept

SqlParameter param4=new SqlParameter("@worAdminDept",SqlDbType.NVarChar) ;

param4.Value=Convert.ToString(req.reqAdminDept);

param4.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd2.Parameters.Add(param4);

//Work Site

SqlParameter param5=new SqlParameter("@worSite",SqlDbType.Int) ;

param5.Value=Convert.ToInt32(req.reqSite);

param5.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd2.Parameters.Add(param5);

//Work Dept

SqlParameter param6=new SqlParameter("@worDept",SqlDbType.NVarChar) ;

param6.Value=Convert.ToString(req.reqDept);

param6.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd2.Parameters.Add(param6);

//worRequest

SqlParameter param7=new SqlParameter("@worRequest",SqlDbType.NText) ;

param7.Value=Convert.ToString(req.reqRequest);

param7.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd2.Parameters.Add(param7);

//worPriority

SqlParameter param8=new SqlParameter("@worPriority",SqlDbType.Int) ;

param8.Value=Convert.ToInt32(req.reqPriority);

param8.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd2.Parameters.Add(param8);

//worCategory

SqlParameter param9=new SqlParameter("@worCategory",SqlDbType.Int) ;

param9.Value=Convert.ToInt32(req.reqCategory);

param9.Direction =ParameterDirection.Input ;

cmdWorkOrderAdd2.Parameters.Add(param9);

//Execute StoredProcedure

cmdWorkOrderAdd2.ExecuteNonQuery();

conn.Close();

<identity impersonate="true"
          userName="domain\user" 
          password="password" />

Microsoft Impersonate Link