Monday, 31 March 2014

Difference Between Having and Where Clause in Sql Server
Both Having Clause and Where clause is used to filter the data coming from the Select statement, but still there are some differences between them. These difference are given below:- To show the difference between the Where Clause and the Having clause we will going to use the table EmployeeDeptInfo whose create query statement is given below :- Create table EmployeeDeptInfo ( Employeeid int, Departmentid int) and it contains the following data Employeeid Departmentid 1 1 2 2 3 2 4 3 3 2 2 2 5 4 2 2 1) Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement. For example, the sql query Update EmployeeDeptInfo Set departmentid =7 Where employeeid=4 will work fine but the query Update EmployeeDeptInfo Set departmentid =7 Having employeeid=4 will not work 2) We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause. For example, the sql query select * from EmployeeDeptInfo where count(employeeid)>1 will not work but the query Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1) will work fine 3) Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ). For Example, in the below sql Query select employeeid, departmentid from EmployeeDeptInfo where employeeid=5 the where clause will search the table EmployeeDeptInfo for the record whose employeeid is 5 and then show the output. but in the below query, Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having employeeid=5 the result are first grouped by the Group By Clause and then they become again filtered by the condition defined in the having clause. Sometime , like above both queries, we get the same result with the help of Where clause and having clause but which way is best is determined automatically by the optimizer and it select the best way of executing it.

Thursday, 27 March 2014

public string Gtrk_UserInformation(BGTrack UserInfo) { string referenceNumber = ""; Ocon = Oconnect.GetConnection(); SqlCommand Ocmd = new SqlCommand("GTRK_CREATETRANSACTIONINFO", Ocon); Ocmd.CommandType = CommandType.StoredProcedure; try { if (Ocmd.Connection.State == ConnectionState.Closed) { Ocmd.Connection.Open(); } Ocmd.Parameters.Clear(); Ocmd.Parameters.AddWithValue("@GTRK_TRUCK_NO", UserInfo.TruckNo); Ocmd.Parameters.AddWithValue("@GTRK_PLANT_CODE", UserInfo.Plantcode); Ocmd.Parameters.AddWithValue("@GTRK_TRUCK_TYPE_CODE", UserInfo.TruckType); Ocmd.Parameters.AddWithValue("@GTRK_TRANSPORTER_CODE", UserInfo.TransType); Ocmd.Parameters.AddWithValue("@GRRK_REMARKS", UserInfo.Remarks); Ocmd.Parameters.AddWithValue("@GTRK_DRIVER_NAME", UserInfo.DriverName); Ocmd.Parameters.AddWithValue("@GTRK_LICENCE_NO", UserInfo.DrivLinceno); Ocmd.Parameters.AddWithValue("@GTRK_MOBILE_NO", UserInfo.DriveMobileno); Ocmd.Parameters.AddWithValue("@GTRK_CREATE_BY", UserInfo.Createby); Ocmd.Parameters.AddWithValue("@FLAG", UserInfo.Flag); Ocmd.Parameters.Add("@referenceNumber", SqlDbType.VarChar, 20).Value = "00000000"; Ocmd.Parameters["@referenceNumber"].Direction = ParameterDirection.Output; Ocmd.CommandTimeout = 0; Ocmd.ExecuteNonQuery(); referenceNumber = Ocmd.Parameters["@referenceNumber"].Value.ToString(); } catch (Exception ex) { referenceNumber = "00000000"; throw ex; } finally { if (Ocmd.Connection.State == ConnectionState.Open) { Ocmd.Connection.Close(); } } return referenceNumber; } using System; using System.Collections; using System.Configuration; using System.Data; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.Services; using System.Text; using BussineLayer; public partial class frm_Gtrk_Glass_Loading : System.Web.UI.Page { BussineLayer.BGTrack ObBLL = new BussineLayer.BGTrack(); string strApp = string.Empty; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { txttruckType.Attributes.Add("ReadOnly", "ReadOnly"); txtTrnsName.Attributes.Add("ReadOnly", "ReadOnly"); txtTruckno.Attributes.Add("autocomplete", "off"); txtTrnsName.Attributes.Add("autocomplete", "off"); txtDrivername.Attributes.Add("autocomplete", "off"); txtLicence.Attributes.Add("autocomplete", "off"); txtmobile.Attributes.Add("autocomplete", "off"); txtRemarks.Attributes.Add("autocomplete", "off"); } } public string GenerateReqNumber() { try { string GenReqNo; Int64 NextNum; DataTable DtGeneReqNo = new DataTable(); DtGeneReqNo = ObBLL.Get_Gtrk_Generatereqno(); if (DtGeneReqNo.Rows.Count > 0 && DtGeneReqNo.Rows[0]["DOC_NO"].ToString() != "") { GenReqNo = DtGeneReqNo.Rows[0]["DOC_NO"].ToString(); NextNum = Convert.ToInt64(GenReqNo); NextNum = NextNum + 1; GenReqNo = Convert.ToString(NextNum); GenReqNo = GenReqNo.PadLeft(10, '0'); return GenReqNo; } else { GenReqNo = "0000000001"; return GenReqNo; } } catch (Exception ex) { return "Error in Generation Request Numbers"; } } [WebMethod] public static string TrlNo(string TruckNo) { string result = ""; BussineLayer.BGTrack ObBLL = new BussineLayer.BGTrack(); DataTable DtTruck = new DataTable(); ObBLL.TruckType = TruckNo.ToString().Trim(); DtTruck = ObBLL.Get_Gtrk_truck_Type(ObBLL); if (DtTruck.Rows.Count > 0) { for (int i = 1; i <= DtTruck.Rows.Count; i++) { result = result + " "; } } else { result = result + " "; } result = result + "
" + DtTruck.Rows[i - 1]["TRUCKTYPE"].ToString() + "
No Records Found...
"; return result; } [WebMethod] public static string TransName(string Transportname) { string result = ""; BussineLayer.BGTrack ObBLL = new BussineLayer.BGTrack(); DataTable DtTrans = new DataTable(); ObBLL.TransName = Transportname.ToString().Trim(); DtTrans = ObBLL.Get_Transport_Name(ObBLL); if (DtTrans.Rows.Count > 0) { for (int i = 1; i <= DtTrans.Rows.Count; i++) { result = result + " "; } } else { result = result + " "; } result = result + "
" + DtTrans.Rows[i - 1]["TRANSNAME"].ToString() + "
No Records Found...
"; return result; } [WebMethod] public static string TrackNumber(string TrackNo) { string result = ""; BussineLayer.BGTrack objBLL = new BussineLayer.BGTrack(); DataTable DtTruck = new DataTable(); objBLL.TruckNo = TrackNo.ToString().Trim(); DtTruck = objBLL.Get_Gtrack_tracknumber(objBLL); if (DtTruck.Rows.Count > 0) { for (int i = 1; i <= DtTruck.Rows.Count; i++) { result = result + " "; } } else { result = result + " "; } result = result + "
" + DtTruck.Rows[i - 1]["GTRK_TRUCK_NO"].ToString() + " " + DtTruck.Rows[i - 1]["TRACKNO"].ToString() + "
No Records Found...
"; return result; } public void btnSave_Click(object sender, EventArgs e) { string[] Transnames = new string[100]; string[] TrackTypes = new string[100]; TrackTypes = txttruckType.Text.Split('-'); Transnames = txtTrnsName.Text.Split('-'); string DocNo = ""; string TruckTime = DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss"); DataTable DtInsInfor = new DataTable(); ObBLL.TruckNo = txtTruckno.Text.ToString().Trim(); ObBLL.Plantcode = Session["Client"].ToString(); ObBLL.TruckType = TrackTypes[0].ToString().Trim(); ObBLL.TransType = Transnames[0].ToString().Trim(); ObBLL.Remarks = txtRemarks.Text.ToString().Trim(); ObBLL.DriverName = txtDrivername.Text.ToString().Trim(); ObBLL.DrivLinceno = txtLicence.Text.ToString().Trim(); ObBLL.DriveMobileno = txtmobile.Text.ToString().Trim(); ObBLL.Createby = Session["UserName"].ToString(); if (Transnames[0].Length==10 && TrackTypes[0].Length==3) { ObBLL.Flag = "IN"; DocNo = ObBLL.Gtrk_UserInformation(ObBLL); } else { ObBLL.Flag = "OUT"; DocNo = ObBLL.Gtrk_UserInformation(ObBLL); } Resetvalue(); if (DocNo != "0000000000" && DocNo.Length != 0) { hdnDocNo.Value = DocNo; hdnDate.Value = TruckTime; ScriptManager.RegisterStartupScript(UPDGlass, this.GetType(), "Click", "SuccessMessage();", true); } else { ScriptManager.RegisterStartupScript(btnSave, btnSave.GetType(), "Failer", "javascript:alert('Wrong user information');", true); } } private void Resetvalue() { txtTruckno.Text = ""; txttruckType.Text = ""; txtTrnsName.Text = ""; txtDrivername.Text = ""; txtLicence.Text = ""; txtmobile.Text = ""; txtRemarks.Text = ""; } } function SuccessMessage(){ if (!alert("Document Successfully Saved.(Document No:" + $(".hdnDocNo").val() +")\n\n"+ "Truck in Entry-Date & Time :" + $(".hdnDate").val() +"")){ var url="PrintDocumentNo.aspx?&DocNo=" + $(".hdnDocNo").val() + ""; window.open(url,'_blank','height=850,width=670,status=yes,toolbar=no,menubar=no,location=no,scrollbars=no,resizable=no,titlebar=no' ); } }

Thursday, 13 March 2014

CREATE PROCEDURE [dbo].[USP_REQUEST_INSERT] ( @XML_INFO NVARCHAR(MAX) = NULL ) AS BEGIN SET NOCOUNT ON BEGIN TRY BEGIN TRANSACTION -- Start the transaction --------------------------- DECLARE @REQUEST_XML INT EXEC SP_XML_PREPAREDOCUMENT @REQUEST_XML OUTPUT, @XML_INFO --1.------INSERT TO T_PROCUREMENT_HEADER-------- INSERT INTO T_EP_PROCUREMENT_HEADER(DOCNUM, IOR_NO, COST_CENTER, SIF_ENTITY_CODE, ESTABLISHMENT_ID, PURPOSE,CURRENCY,TOTAL_AMOUNT,TAX_AMOUNT,GROSS_AMOUNT, STATUS,APP_LEVEL, ENTERED_DATE, ENTERED_BY,REMARKS) SELECT DOCNUM, IOR_NO, COST_CENTER,SIF_ENTITY_CODE, ESTABLISHMENT_ID, PURPOSE,CURRENCY,TOTAL_AMOUNT,TAX_AMOUNT,GROSS_AMOUNT,STATUS, APP_LEVEL, getdate(), ENTERED_BY ,REMARKS FROM OPENXML (@REQUEST_XML , '/EPROC/HEADER' , 1) WITH ( DOCNUM VARCHAR(22)'DOCNUM/text()' , IOR_NO VARCHAR(22)'IOR_NO/text()' , COST_CENTER VARCHAR(10)'COST_CENTER/text()' , SIF_ENTITY_CODE VARCHAR(10)'SIF_ENTITY_CODE/text()' , ESTABLISHMENT_ID VARCHAR(10)'ESTABLISHMENT_ID/text()' , PURPOSE VARCHAR(250)'PURPOSE/text()' , CURRENCY VARCHAR(10)'CURRENCY/text()' , TOTAL_AMOUNT VARCHAR(10)'TOTAL_AMOUNT/text()' , STATUS VARCHAR(15)'STATUS/text()' , APP_LEVEL VARCHAR(100)'APP_LEVEL/text()' , TAX_AMOUNT VARCHAR(10)'TAX_AMOUNT/text()' , GROSS_AMOUNT VARCHAR(10)'GROSS_AMOUNT/text()' , ENTERED_BY VARCHAR(10)'ENTERED_BY/text()' , REMARKS VARCHAR(250)'REMARKS/text()' ) --2.-------------Insert to T_PROCUREMENT_DETAILS -------------- INSERT INTO T_EP_PROCUREMENT_DETAILS (DOCNUM,LINE_ITEM,TYPE,DESCRIPTION,EXP_CATEGORY,UOM,QUANTITY,UNIT_PRIZE,NET_AMOUNT) SELECT DOCNUM, LINE_ITEM,TYPE,DESCRIPTION, EXP_CATEGORY, UOM, QUANTITY, UNIT_PRIZE,NET_AMOUNT FROM openxml(@REQUEST_XML, '/EPROC/DETAILS', 1) WITH ( DOCNUM VARCHAR(10) 'DOCNUM /text()', LINE_ITEM VARCHAR(9) 'LINE_ITEM /text()', DESCRIPTION VARCHAR(150) 'DESCRIPTION /text()', TYPE VARCHAR(10)'TYPE/text()' , EXP_CATEGORY VARCHAR(10)'EXP_CATEGORY/text()' , UOM VARCHAR(10) 'UOM /text()', QUANTITY VARCHAR(15) 'QUANTITY/text()', UNIT_PRIZE VARCHAR(15) 'UNIT_PRIZE/text()', NET_AMOUNT VARCHAR(9) 'NET_AMOUNT/text()') SELECT 'SUCCESS' AS OUTPUT -- If we reach here , success! COMMIT END TRY --END TRY-------------------------------------------------------------- BEGIN CATCH -- Whoops , there was an error IF @@TRANCOUNT > 0 ROLLBACK -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000) , @ErrSeverity int SELECT @ErrMsg = error_message() , @ErrSeverity = error_severity() RAISERROR(@ErrMsg , @ErrSeverity , 1) END CATCH SET NOCOUNT OFF END

Saturday, 8 March 2014

Linq Program
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> ::LINQ PROGRAM::
Customer Code
Customer Name

Customer Name <%#Eval("CustomerId")%> Customer Code <%#Eval("CustomerCode")%>
Asp.Cs page (Linq Insert,Delete,Update) using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { getdetails(); } //Select Query private void getdetails() { DataClassesDataContext cons = new DataClassesDataContext(); var cust = from custom in cons.customer_Headers select new { custom.CustomerId, custom.CustomerCode }; gvlist.DataSource = cust; gvlist.DataBind(); } //Insert Query protected void btnclick_Click(object sender, EventArgs e) { DataClassesDataContext cons = new DataClassesDataContext(); customer_Header sam = new customer_Header { CustomerCode = Convert.ToInt32(txtcustcode.Text), CustomerId = txtcustid.Text }; cons.customer_Headers.InsertOnSubmit(sam); cons.SubmitChanges(); getdetails(); } //Delete Query protected void BtnDelete_Click(object sender, EventArgs e) { DataClassesDataContext cons = new DataClassesDataContext(); Int32 str = Convert.ToInt32(txtcustcode.Text); customer_Header custs = (from cuss in cons.customer_Headers where cuss.CustomerCode == (str) select cuss).Single(); cons.customer_Headers.DeleteOnSubmit(custs); cons.SubmitChanges(); getdetails(); } //Update Query protected void btnUpdate_Click(object sender, EventArgs e) { DataClassesDataContext cons = new DataClassesDataContext(); Int32 strs = Convert.ToInt32(txtcustcode.Text); customer_Header custUp = (from cus in cons.customer_Headers where cus.CustomerCode == (strs) select cus).SingleOrDefault(); custUp.CustomerId = txtcustid.Text; cons.SubmitChanges(); getdetails(); } }

Wednesday, 5 March 2014

Connection String: /b> using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.SqlClient; namespace TierLayer { public class Connection { public static string StrConnSql;//For Sql public SqlConnection GetConnection() { StrConnSql = ConfigurationManager.ConnectionStrings["Con"].ToString(); SqlConnection Connection = new SqlConnection(StrConnSql); return Connection; } } } DAL using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace TierLayer { public class DataAccessLayer { Connection con = new Connection(); public int Insertdate(BusinessProperties Bp) { int rows = 0; SqlConnection conn = new SqlConnection(); conn = con.GetConnection(); SqlCommand cmd = new SqlCommand("tire_demo", conn); cmd.CommandType = CommandType.StoredProcedure; try { if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); } cmd.Parameters.AddWithValue("@Names", Bp.Name); cmd.Parameters.AddWithValue("@Mobileno", Bp.Mobileno); rows = cmd.ExecuteNonQuery(); } catch (Exception ex) { rows = 0; throw ex; } finally { if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); } } return rows; } } } BLL using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.IO; namespace TierLayer { public class BusineeLogic { public int Insertdate1(BusinessProperties Bp) { DataAccessLayer DL = new DataAccessLayer(); try { return DL.Insertdate(Bp); } catch (Exception ex) { throw ex; } } } } BEL using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace TierLayer { public class BusinessProperties { private string name; public string Name { get { return name; } set { name = value; } } private string mobileno; public string Mobileno { get { return mobileno; } set { mobileno = value; } } } } User Access Layer using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using TierLayer; namespace _3tier_Arch { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnenter_Click(object sender, EventArgs e) { int rowss = 0; BusinessProperties Bp = new BusinessProperties(); Bp.Name = txtname.Text; Bp.Mobileno = txtmobile.Text; BusineeLogic Bl = new BusineeLogic(); rowss= Bl.Insertdate1(Bp); if (rowss.ToString()=="1") { ScriptManager.RegisterStartupScript(btnenter, btnenter.GetType(), "msg", "javascript:alert('Saved Successfully');", true); } } } } User Access Layer <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="_3tier_Arch._Default" %> 3tier
Name
Mobile No