Unique Vs. Primary Key - Referential Integrity
When we create a Referential integrity, Parent column can be a PRIMARY KEY or UNIQUE column!
OK.
1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(10)
)
GO
2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO
So, The above statement uses only the Parent Table name. But, not Primary Key column.
By default, It refers the PRIMARY KEY column of the reference table(MasterTable1). So, no need to give the PRIMARY KEY column name explicitly.
But, Have you tried with UNIQUE key for the same scenario ?
1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id INT IDENTITY(1,1) UNIQUE,
Column1 VARCHAR(10)
)
GO
2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO
You will get an Err message!
Msg 1773, Level 16, State 0, Line 1
Foreign key 'FK__ChildTabl__Maste__658C0CBD' has implicit reference to object 'MasterTable1' which does not have a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
So, when referring an UNIQUE column, It should be TableName(ColumnName)
CREATE TABLE ChildTable1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1(Id),
Column2 VARCHAR(10)
)
GO
So, Using TableName(columnName) is mandatory when referring an UNIQUE key column!!!
Monday, 15 September 2014
Saturday, 31 May 2014
Asp.Net Application Send Mail Sample Code Project
using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Text;
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.Net.Mail;
using System.Runtime.InteropServices;
using System.Xml;
using System.Globalization;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection Oconnection = new SqlConnection(ConfigurationManager.AppSettings["Con"].ToString());
DataTable DTMailTrigger = new DataTable();
string MailTxt = "";
protected void Page_Load(object sender, EventArgs e)
{
//Session["SGID"] = "N8222335"; //N8222335
MailReports();
}
protected void MailReports()
{
try
{
SqlCommand cmd = new SqlCommand("USP_PENDING_INVOICE_STATIDCS", Oconnection);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sqldat = new SqlDataAdapter(cmd);
sqldat.Fill(DTMailTrigger);
if (DTMailTrigger.Rows.Count > 0)
{
MailTxt = "
"; MailTxt += " "; MailTxt += " "; MailTxt += " "; MailTxt += "
";
MailTxt += "
"; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; } for (int i = 0; i < DTMailTrigger.Rows.Count; i++) { MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += "
";
Response.Write(MailTxt);
//Details To send a Mail using NET MAIL
MailMessage mail = new MailMessage();
mail.Subject = "PENDING INVOICE STATISTICS-SEKURT INDIA LTD.,"; //Subject
mail.From = new MailAddress("Nagarajan.R2@saint-gobain.com");
//mail.From = new MailAddress("Pending Invoice Statistics-Sekurt "); //From Address
//Send To Mail id
string[] strmailTO = { "Nagarajan.R2@saint-gobain.com", "Nagarajan.R2@saint-gobain.com", "nagarajan027@gmail.com" }; //TO Address
for (int j = 0; j < strmailTO.Length; j++)
{
mail.To.Add(strmailTO[j]);
}
//Send CC Mail id
//string[] strmailCC = {""};
//for (int s = 0; s < strmailCC.Length; s++)
//{
// mail.CC.Add(strmailCC[s]);
//}
//Send BCC Mail id
string[] strmailBCC = { "Nagarajan.R2@saint-gobain.com" }; //BCC Address
for (int k = 0; k < strmailBCC.Length; k++)
{
// mail.Bcc.Add(strmailBCC[k]);
}
mail.Body = (MailTxt);
mail.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "10.87.10.11";
smtp.Send(mail);
}
}
catch (SqlException ex)
{
string errorInfo = "";
errorInfo += "";
errorInfo += "
";
MailMessage mail = new MailMessage();
mail.Subject = "PENDING INVOICE STATISTICS-SEKURT INDIA"; //Subject
mail.From = new MailAddress("Pending Invoice Statistics-Sekurt "); //From Address
string[] strmailTO = { "Nagarajan.R2@saint-gobain.com" }; //TO Address
for (int i = 0; i < strmailTO.Length; i++)
{
mail.To.Add(strmailTO[i]);
}
//Send CC Mail id
//string[] strmailCC = { "Lavanya.C@saint-gobain.com" };
//for (int s = 0; s < strmailCC.Length; s++)
//{
// mail.CC.Add(strmailCC[s]);
//}
//send BCC mail id Error Message
string[] strmailBCC = { "Nagarajan.R2@saint-gobain.com" }; //BCC Address
for (int j = 0; j < strmailBCC.Length; j++)
{
// mail.Bcc.Add(strmailBCC[j]);
}
mail.Body = (errorInfo);
mail.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "10.87.10.11";
smtp.Send(mail);
}
}
}
"; MailTxt += " "; MailTxt += " "; MailTxt += " "; MailTxt += "
| "; MailTxt += " PENDING FOR INVOICE STATISTICS-SEKURT INDIA "; MailTxt += " (Report as on: " + DateTime.Now.ToString("dd-MMM-yyyy hh:mm tt") + ") "; MailTxt += " |
"; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; } for (int i = 0; i < DTMailTrigger.Rows.Count; i++) { MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += " "; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += ""; MailTxt += "
| Status | Total | |
| Invoice Pending for OCR | " + DTMailTrigger.Rows[0][0].ToString() + " | SSC-Team |
| Invoice Reject | " + DTMailTrigger.Rows[0][1].ToString() + " | FSG-Team |
| Invoice Pending For EDIFACT | " + DTMailTrigger.Rows[0][2].ToString() + " | IT-Team |
| Invoice Pending For Integration | " + DTMailTrigger.Rows[0][3].ToString() + " | IT-Team |
";
errorInfo += " ";
errorInfo += "";
errorInfo += " ";
errorInfo += "
|
Wednesday, 28 May 2014
Classic ASP Page
Sample Page
Post Next page
Stored Procedure and Retrieval Date
Classic ASP page
<%
'Variable Declaration
Dim strcon,sqry,StrGender,SQL,names,rs,cmd,ORC,adocmd,conn
'Connection String
Set strcon=Server.CreateObject("ADODB.Connection")
'Database Connection String
strcon.open "PROVIDER=SQLOLEDB.1;DATA SOURCE=L04webche012;UID=demo;PWD=demo;database=Demo"
'Parameter
Set adocmd = Server.CreateObject("ADODB.Command")
With adocmd
Set .ActiveConnection = strcon
'Stored procedure Value
.CommandType = 4
.CommandTimeout = 180
'Procedure Name
.CommandText = "sample_getvalue"
'Parameter Value
.Parameters.Append .CreateParameter("@names", 200, 1, 30, Request.Form("Name"))
Set ORC = .Execute
response.Write "Successfull"
End With
%>
| id | Name |
| <%= RS.Fields("id")%> | <%= RS.Fields("names")%> |
Tuesday, 27 May 2014
Asp.Net Design page
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true"
CodeFile="frm_Gtrk_Glass_Loading.aspx.cs" Inherits="frm_Gtrk_Glass_Loading" Title="G-Track Glass Loading" %>
<%@ Register Assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
Namespace="System.Web.UI" TagPrefix="asp" %>
Asp.Net CS Page
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
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 + "
";
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 + "
";
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 + "
";
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 = "";
}
}
Jquery Validation
function pageLoad()
{
$("input[type='text']").live("keydown", function(){ $(this).css({ "background-color": "#FFF", "border": "1px solid #C0C0C0" }); });
$(".imgtrucktype").click(function() {
$(".divAutoContent").html("");
$(".divProjectPopup").show();
});
$(".imgSearch").click(function(evt){
evt.preventDefault();
var Gtrkno=$(".txtTypeCode").val();
$.ajax({
type : "POST",
contentType : "application/json; charset=utf-8",
dataType: "json",
url: "frm_Gtrk_Glass_Loading.aspx/TrlNo",
data: '{"TruckNo":"'+Gtrkno+'"}',
success :function(res){
$("#divAutoContent").html(res.d);
},
error: function(){
alert("Error Ajax");
}
});
});
$(".trucktypes").live("click",function(){
$(".txttruckType").css({ "background-color": "#FFF", "border": "1px solid #C0C0C0" });
var reqNo = $(this).parents("tr").find(".trucktypes").html();
var truck=reqNo.split('-');
$(".txttruckType").val(truck.join('-'));
//$(".txttruckType").val(truck[1]);
// $(".lblTruckType").text(truck[1]);
$(".divProjectPopup").hide();
if($(".txttruckType").val().length!=0){
$(".txttruckType").attr("disabled",true);
}
else
{
$(".txttruckType").attr("enabled",false);
}
});
$(".modelPopupClose").click(function(){
$(".divProjectPopup").hide();
$(".divProjectPopup1").hide();
$(".divProjectPopup2").hide();
});
$(".imgTranname").click(function(evt) {
evt.preventDefault();
$(".divAutoContent1").html("");
$(".divProjectPopup1").show();
});
$(".imgTranSech").click(function(evt){
evt.preventDefault();
var Gtranname=$(".txtTransName").val();
$.ajax({
type : "POST",
contentType : "application/json; charset=utf-8",
dataType: "json",
url: "frm_Gtrk_Glass_Loading.aspx/TransName",
data: '{"Transportname":"'+Gtranname+'"}',
success :function(res){
$("#divAutoContent1").html(res.d);
},
error: function(){
alert("Error Ajax");}
});
});
$(".transname").live("click",function(){
$(".txtTrnsName").css({ "background-color": "#FFF", "border": "1px solid #C0C0C0" });
var Reqname =$(this).parents("tr").find(".transname").html();
var Trans=Reqname.split('-');
// $(".txtTrnsName").val(Trans.shift());
$(".txtTrnsName").val(Trans.join('-'));
// $(".lblTranname").text(Trans.join('-'));
$(".divProjectPopup1").hide();
if($(".txtTrnsName").val().length!=0){
$(".txtTrnsName").attr("disabled",true);
}
else
{
$(".txtTrnsName").attr("enabled",false);
}
});
$(".btnDediTruck").click(function(){
$(".divAutoContent2").html("");
$(".divProjectPopup2").show();
});
$(".imgTracknumber").click(function(evt){
evt.preventDefault();
var GetTruckNo=$(".txtTrackNumber").val();
$.ajax({
type : "POST",
contentType : "application/json; charset=utf-8",
dataType: "json",
url: "frm_Gtrk_Glass_Loading.aspx/TrackNumber",
data: '{"TrackNo":"'+GetTruckNo+'"}',
success :function(res){
$("#divAutoContent2").html(res.d);
},
error: function(){
alert("Error Ajax");}
});
});
$(".TrackNo").live("click",function(){
$(".txtTruckno").css({ "background-color": "#FFF", "border": "1px solid #C0C0C0" });
//var Track =$(this).parents("tr td.eq(1)").find(".TrackNo").html();
var Track=$(this).closest('tr').children('td:eq(1)').text();
var TrackNos=Track.split('^');
$(".txtTruckno").val(TrackNos[0]);
$(".txttruckType").val(TrackNos[2]);
$(".txtTrnsName").val(TrackNos[1]);
$(".divProjectPopup2").hide();
if($(".txttruckType").val().length!=0 || $(".txtTrnsName").val().length != 0 || $(".txtTruckno").val().lenght!=0){
$(".txttruckType").attr("disabled",true);
$(".txtTrnsName").attr("disabled",true);
$(".txtTruckno").attr("disabled",true);
}
else
{
$(".txttruckType").attr("enabled",false);
$(".txtTrnsName").attr("enabled",false);
$(".txtTruckno").attr("enabled",false);
}
});
$(".btnSave").click(function(){
//alert($(".divProjectPopup").attr("style"));
if($(".divMask").is(":visible")){
return false;
}
if($(".txtTruckno").val().length == 0)
{
alert("Please enter Truck No.");
$(".txtTruckno").focus();
$(".txtTruckno").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txttruckType").val().length == 0)
{
alert("Please enter Truck type");
$(".txttruckType").focus();
$(".txttruckType").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txtTrnsName").val().length == 0)
{
alert("Please enter Transport name");
$(".txtTrnsName").focus();
$(".txtTrnsName").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txtDrivername").val().length == 0)
{
alert("Please enter Driver name");
$(".txtDrivername").focus();
$(".txtDrivername").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txtLicence").val().length == 0)
{
alert("Please enter Licence no");
$(".txtLicence").focus();
$(".txtLicence").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txtmobile").val().length == 0)
{
alert("Please enter Mobile no");
$(".txtmobile").focus();
$(".txtmobile").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
if($(".txtmobile").val().length<10)
{
alert("Please enter ten digital mobile number");
$(".txtmobile").focus();
$(".txtmobile").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
});
$(".txtmobile,.txtDocno").live("keydown",function(evt){
var n=evt.keyCode;
if(!((n==8) || (n==9) || (n>=46 && n<=57) || (n>=96 && n<=105)))
evt.preventDefault();
});
$(".txtTruckno").bind("keypress", function (event) {
var regex = new RegExp("^[a-zA-Z0-9]+$");
var key = String.fromCharCode(!event.charCode ? event.which : event.charCode);
if (!regex.test(key)) {
event.preventDefault();
return false;
}
});
$(".btnReset").click(function(){
Resetvalue();
});
$(".sapnlnkDoc").click(function(){
var PrintDocumentNo =$(this).parents("tr").find(".sapnlnkDoc").html();
//var a=this($(".sapnlnkDoc").text());
var url="PrintDocumentNo.aspx?&DocNo=" + PrintDocumentNo + "";
window.open(url,'_blank','height=830,width=700,status=yes,toolbar=no,menubar=no,location=no,scrollbars=yes,resizable=no,titlebar=no' );
});
//----------------------------------------------Truck out Entry ---------------------//
$(".btnTrckOutentry").click(function(){
if($(".txtTruckOutDoc").val().length==0)
{
alert("Please enter Document No.");
$(".txtTruckOutDoc").focus();
$(".txtTruckOutDoc").css("background-color", "#FFE8E8").css("border", "1px solid #FF6666");
return false;
}
});
$(".btnTruckExit").click(function(){
var url="frm_GTrack_Home.aspx";
window.open(url,"_self");
});
$(".btnResets").click(function(){
$(".txtDocno").val("");
});
}
function Resetvalue()
{
$(".txtTruckno").val("");
$(".txttruckType").val("");
$(".txtTrnsName").val("");
$(".txtDrivername").val("");
$(".txtLicence").val("");
$(".txtmobile").val("");
$(".txtRemarks").val("");
}
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' );
}
}
| Incoming Trucks for Picking Materials | ||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||
|
| " + DtTruck.Rows[i - 1]["TRUCKTYPE"].ToString() + " |
| No Records Found... |
| " + DtTrans.Rows[i - 1]["TRANSNAME"].ToString() + " |
| No Records Found... |
| " + DtTruck.Rows[i - 1]["GTRK_TRUCK_NO"].ToString() + " |
| No Records Found... |
Number Ranger
CREATE TABLE [dbo].[M_NUMBER_RANGE](
[GTRK_NAME1] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[GTRK_SUBNAME] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_PREFIX_LETTER] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_STARTING_NUMNER] [numeric](18, 0) NULL,
[GTRK_ENDING_NUMBER] [numeric](18, 0) NULL,
[GTRK_CURRENT_NUMBER] [numeric](18, 0) NULL,
[GTRK_INCREMENT] [numeric](18, 0) NULL,
[GTRK_PERCENTAGE] [numeric](18, 0) NULL,
[GTRK_NUMBER_LENGTH] [numeric](18, 0) NULL,
[GTRK_GROUP_TABLE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_FLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_REMARKS] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_ENTEREDDT] [datetime] NULL,
[GTRK_ENTEREDBY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GTRK_MODIFIEDDT] [datetime] NULL,
[GTRK_MODIFIEDBY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[GTRK_NAME1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GTRACK-V2 GTRACK-V2 10000000 99999999 543 1 0 8 GTRK_TRUCK_DETAILS NULL NULL 2014-01-29 11:15:46.857 ADMIN NULL NULL
Ranger Procedure :
/*************************************************************
CREATED BY : NAGARAJAN.R
CREATED DATE : 29/01/2014
PURPOSE : Get the Maximum Doc Number
EXECUTION : USP_GET_DOCNO 'GTRACK-V2','',''
*************************************************************/
CREATE PROCEDURE USP_GET_DOCNO
(
@ID VARCHAR(25) = NULL,
@DOCNO VARCHAR(250) OUTPUT,
@INDEXNO VARCHAR(9) OUTPUT
)
AS
BEGIN
SELECT @DOCNO = GTRK_PREFIX_LETTER + '' + convert(VARCHAR, GTRK_STARTING_NUMNER + (GTRK_CURRENT_NUMBER + GTRK_INCREMENT))
, @INDEXNO = (GTRK_CURRENT_NUMBER + GTRK_INCREMENT)
FROM
M_NUMBER_RANGE
WHERE
GTRK_NAME1 = @ID
SELECT @DOCNO AS DOCNO
, @INDEXNO AS INDEXNO
END
Automation Generated Number
--EXEC GTRK_CREATETRANSACTIONINFO'TNK4385784','839','HPL','RNJ','TEST','Nagarajan','TN4356','9842946063','Nagu','IN'
CREATE PROC [dbo].[GTRK_CREATETRANSACTIONINFO]
(
@GTRK_TRUCK_NO VARCHAR(15),
@GTRK_PLANT_CODE VARCHAR(15),
@GTRK_TRUCK_TYPE_CODE VARCHAR(100),
@GTRK_TRANSPORTER_CODE VARCHAR(100),
@GRRK_REMARKS VARCHAR(250),
@GTRK_DRIVER_NAME VARCHAR(50),
@GTRK_LICENCE_NO VARCHAR(15),
@GTRK_MOBILE_NO VARCHAR(10),
@GTRK_CREATE_BY VARCHAR(50),
@FLAG VARCHAR(10),
@referenceNumber VARCHAR(15)=NULL OUTPUT
)
AS
BEGIN
--DECLARE @AUTOMATION_DOCNO VARCHAR(10)
--SELECT @AUTOMATION_DOCNO=isnull(MAX(ISNULL(GTRK_DOC_NO,00000000001)+1),0000000001) FROM GTRK_TRUCK_DETAILS
--Find Maximum Order Number
--DECLARE @OrderNumber Varchar(50)
--SELECT @OrderNumber =CAST(ISNULL(Max(GTRK_DOC_NO),0) + 1 AS VARCHAR(40)) FROM GTRK_TRUCK_DETAILS
--SELECT @OrderNumber=STUFF('00000000', 8-LEN(@orderNumber)+1, LEN(@orderNumber),@orderNumber)
-- SELECT @OrderNumber
DECLARE @OrderNumber VARCHAR(10)
DECLARE @CURRENTNO VARCHAR(9)
EXEC USP_GET_DOCNO 'GTRACK-V2', @OrderNumber OUTPUT ,@CURRENTNO OUTPUT
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO GTRK_TRUCK_DETAILS(GTRK_DOC_NO,GTRK_TRUCK_NO,GTRK_PLANT_CODE,GTRK_TRUCK_TYPE_CODE,GTRK_TRANSPORTER_CODE,GTRK_MATL_MVMNT_CODE,GTRK_MATL_CODE,GTRK_REMARKS,GTRK_TRUCK_IN,GTRK_DRIVER_NAME,
GTRK_LICENCE_NO,GTRK_MOBILE_NO,GTRK_CREATE_BY,GTRK_CREATED_DATE,GTRK_MODIFIED_BY,GTRK_MODIFIED_DATE,GTRK_FLAG_TYPE)
VALUES(@OrderNumber,@GTRK_TRUCK_NO,@GTRK_PLANT_CODE,@GTRK_TRUCK_TYPE_CODE,@GTRK_TRANSPORTER_CODE,'10','10000001',@GRRK_REMARKS,getdate(),@GTRK_DRIVER_NAME,
@GTRK_LICENCE_NO,@GTRK_MOBILE_NO,@GTRK_CREATE_BY,getdate(),NULL,NULL,@FLAG)
set @referenceNumber=@OrderNumber
----------------UPDATE Current Number range table---------------
UPDATE M_NUMBER_RANGE SET GTRK_CURRENT_NUMBER = @CURRENTNO WHERE GTRK_NAME1 ='GTRACK-V2'
----------------------------------------------------------------
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage
END CATCH
END
--select *From GTRK_TRUCK_DETAILS
--0010953525
Trigger
1)When you create a trigger you have to identify event and action of your trigger
2)A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. .
3) A trigger is triggered automatically.
4)It does not pass parameters
Stored procedure
1)Explicitly executed by invoking call to SP
2)you can not join SP
3)We can call a stored procedure from front
4)Stored procedure can take the input parameters
Function
1)can be used with Select statement
2)Not returning output parameter but returns Table variables
3) Cannot have transaction within function
4)Cannot be used to change server configuration
Difference between Stored Procedure and Function
1) Procedure can return zero or n values whereas function can return one value which is mandatory (Read more Here).
2) Procedures can have input, output parameters for it whereas functions can have only input parameters.
3) Procedure allows select as well as DML(INSERT/UPDATE/DELETE) statements in it whereas function allows only select statement in it.
4) Functions can be called from procedure whereas procedures cannot be called from function.
5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6) We can go for transaction management in procedure whereas we can't go in function.
7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.
Array List
1.Array List is a List.
2.In Array list we can only add items to the list.
3.Here we Can Add any datatype value,Every item in Arraylist is treated as object.
4.Index Numeric.
5.Data in Arraylist is stored in as only the value.
6.Slow when come to search
Hash Table
1.Hash Table is a map.
2.In Hashtable we can add data with the key.
3.Retrieving by key in Hashtable is faster than retrieving in Arraylist.
4.Index can be string also.
5.Data in Hashtable is stored in the key-value pair.
6.Fast when searching.
So lets first start with the difference between 1.0 and 2.0.
Support for 64 bit application.
Generics
SQL cache dependency
Master pages
Membership and roles
Now the next difference .NET 2.0 and 3.0
=========================================
WCF
WPF
WWF
WCS ( card space)
3.0 and 3.5
==========================================
LINQ
Ajax inbuilt
ADO Entity framework
ADO data services
Multi targeting
Finally 3.5 and 4.0
===========================================
MEF
Parallel computing
DLR dynamic
Code contract
language runtime
Lazy initialization
Custom controls: A control authored by a user or a third-party software vendor that does not belong to the .NET Framework class library. This is a generic term that includes user controls. A custom server control is used in Web Forms (ASP.NET pages). A custom client control is used in Windows Forms applications.
User Controls: In ASP.NET: A user-authored server control that enables an ASP.NET page to be re-used as a server control. An ASP.NET user control is authored declaratively and persisted as a text file with an .ascx extension. The ASP.NET page framework compiles a user control on the fly to a class that derives from the System.Web.UI.UserControl class.
Difference between Sql server 2005 and 2008
Sr No SQL Server 2005 SQL Server 2008
1 XML datatype is introduced. XML datatype is used.
2 Can not encrypt the entire database. Can encrypt the entire database introduced in 2008.
3 Datetime is used for both date and time. Date and time are seperately used for date and time
4 No table datatype is included. Table datatype introduced.
5 SSIS is started using. SSIS avails in this version.
6 CMS is not available. Central Management Server(CMS) is Introduced.
7 PBM is not available Policy based management(PBM) server is Introduced
What is the difference between unique key and primary key?
Unique Key Primary Key
Unique key can have nulls Primary key cannot have nulls.
In a single table we can create multiple unique keys. In a single table we can have only one primary key.
It creates a non-clustered index by default. It created a clustered index by default.
Both unique keys and primary keys can be referenced by foreign key.
View State:
View state is maintained in page level only.
View state of one page is not visible in another page.
View state information stored in client only.
View state persist the values of particular page in the client (browser) when post back operation done.
View state used to persist page-instance-specific data. (Read more)
Session State:
Session state is maintained in session level.
Session state value is available in all pages within a user session.
Session state information stored in server.
Session state persist the data of particular user in the server. This data available till user close the browser or session time completes.
Session state used to persist the user-specific data on the server side. (Read more)
Usage
If you want to access the information on different web pages, you can use SessionState- If you want to access from the same page, then you can use Viewstate
Security
Session state provides more security when compared with view state as the data value is stored in server side
Convert .tostring() and variable.tostirng()
The basic difference between them is “Convert.ToString(variable)” handles NULL values even if variable value become null but “variable.ToString()” will not handle NULL values it will throw a NULL reference exception error. So as a good coding practice using “convert” is always safe.
Example
//Returns a null reference exception for str.
string str;
object i = null;
str = i.ToString();
//Returns an empty string for str and does not throw an exception. If you dont
string str;
object i = null;
str = Convert.ToString(i);
Difference between GET and POST Methods?
GET Method ():
1) Data is appended to the URL.
2) Data is not secret.
3) It is a single call system
4) Maximum data that can be sent is 256.
5) Data transmission is faster
6) this is the default method for many browsers
POST Method ():
1) Data is not appended to the URL.
2) Data is Secret
3) it is a two call system.
4) There is no Limit on the amount of data. That is characters any amount of data can be sent.
5) Data transmission is comparatively slow.
6) No default and should be explicitly specified.
Difference between authorization and authentication?
Authorization is a process of allowing or denying resources to particular user or record
Declaration of authorization is
Sometimes authorization allows the unauthorized persons at that time we will use
Authentication means
Authentication is a process where we identify the credentials of user i.e. username, password and create an identity to mention user as an authenticated.
Difference between view state and hidden field?
viewstate is secured hidden field is insecure
Viewstate will store large amount of data but hidden filed will store small amount of data.
Difference between Abstract Class and Interface
In abstract class method can have definition as well as declaration also. But Interface should have only definition.
All the Methods are Public as default and don’t have any access Modifier Controls in interface, whereas for abstract class we can have access modifier for methods.
Abstract class can have constructor or destructor, whereas interface not.
Abstract class can’t be part of multiple inheritance and we can implement multiple interface.
Differences between Array list and Hash table?
Hash table store data as name, value pair. While in array only value is store.
To access value from hash table, you need to pass name. While in array, to access value, you need to pass index number.
you can store different type of data in hash table, say int, string etc. while in array you can store only similar type of data.
Database count to send mail daily reports
CREATE PROC SEND_SEKURIT_PENDING_AUTOMAIL
AS
BEGIN
SELECT A.ORD, A.NAMEE,A.AGEING,A.Pending_For_OCR_DW01,B.Pending_For_OCR_DW02,NULL as NC01, NULL as NC02,A.Pending_For_OCR_DW01+B.Pending_For_OCR_DW02 as Total,A.Responsibility
INTO #TEMPSEKURITMAILALTER FROM
(
SELECT 1 as ORD,'OCR Process' as NAMEE, '<2' AS AGEING, count(BARCODE_NO) AS Pending_For_OCR_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW01'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 1 as ORD, 'OCR Process' as NAMEE, '>2'AS AGEING,count(BARCODE_NO)AS Pending_For_OCR_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW01'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 1 as ORD,'OCR Process' as NAMEE, '>2'AS AGEING,count(BARCODE_NO)AS Pending_For_OCR_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='SCANNED'AND COMPANY_CODE='DW02'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 2 as ORD,'Clarification' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS Rejection_DW01,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW01' AND REJECT_REASON <>'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'1'
)as C
LEFT OUTER JOIN
(
SELECT 2 as ORD, 'Clarification' as NAMEE,'<2'AS AGEING, count(BARCODE_NO)AS Rejection_OCR_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW02' AND REJECT_REASON <>'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 2 as ORD,'Clarification' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS Rejection_DW02,'SSC' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS NOT IN('REJECTED','DELETED') AND COMPANY_CODE='DW02' AND REJECT_REASON <>'1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'1'
)as D
ON C.AGEING=D.AGEING
UNION
SELECT E.ORD,E.NAMEE,E.AGEING,E.EDI_Pending_DW01,F.EDI_Pending_DW02,NULL as NC01, NULL as NC02,E.EDI_Pending_DW01+F.EDI_Pending_DW02 as Total,E.Responsibility
FROM
(
SELECT 3 as ORD,'EDIFACT Creation' as NAMEE,'<2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW01' AND REJECT_REASON ='1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)>getdate()-2
UNION
SELECT 3 as ORD,'EDIFACT Creation' as NAMEE,'>2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW01' AND REJECT_REASON ='1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 3 as ORD,'EDIFACT Creation' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS EDI_Pending_DW02,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='OCR' AND COMPANY_CODE='DW02' AND REJECT_REASON ='1'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 4 as ORD,'SAP Integration' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS AS2_Pending_DW01,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='EDI' AND COMPANY_CODE='DW01'
AND convert(varchar,INVOICE_SCANNING_DATE,112)getdate()-2
UNION
SELECT 4 as ORD,'SAP Integration' as NAMEE, '>2'AS AGEING, count(BARCODE_NO)AS AS2_Pending_DW02,'IT' AS Responsibility FROM T_SEK_INVOICE_DETAILS
WHERE INVOICE_STATUS ='EDI' AND COMPANY_CODE='DW02'
AND convert(varchar,INVOICE_SCANNING_DATE,112)'
SET @Body =@Body +''
SET @Body =@Body +'
'
--SET @Body = @Body + '
'
PRINT @Body
DROP TABLE #TEMPSEKURITMAILALTER
--
--EXEC msdb.dbo.sp_send_dbmail
--
--@recipients = 'Vivek.Srivatsan@saint-gobain.com;Muralidaran.M@saint-gobain.com;Prithviraj.S@saint-gobain.com;Sujoy.Mukherjee@saint-gobain.com;Parthasarathi.S@saint-gobain.com;Gowdhami.M@saint-gobain.com;Chandra.G@saint-gobain.com',
--@copy_recipients = 'Sundaram.DR@saint-gobain.com;Jagathratchagan.J@saint-gobain.com;Nagarajan.R2@saint-gobain.com',
--@subject = @Subject,
--@body = @Body,
--@body_format = 'HTML'
END
| ' SET @Body =@Body +'Sekurit-Pending Invoice Statistics | |||||||
| Status | ' SET @Body =@Body +'Ageing | ' SET @Body =@Body +'DW01 | ' SET @Body =@Body +'DW02 | ' SET @Body =@Body +'NC01 | ' SET @Body =@Body +'NC02 | ' SET @Body =@Body +'Total | ' SET @Body =@Body +'Responsibility |
| '+ISNULL(@NAME,'')+' | ' END SET @Body=@Body+''+ISNULL(@AGEING,'')+' | ' SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW01,'')+' | ' SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW02,'')+' | ' SET @Body=@Body+''+ISNULL(@NC01,0)+' | ' SET @Body=@Body+''+ISNULL(@NC02,0)+' | ' SET @Body=@Body+''+ISNULL(@TOTAL,0)+' | ' SET @Body=@Body+''+ISNULL(@RESPONSIBILITY,'')+' | ' SET @Body=@Body+'
| '+ISNULL(@NAME,'')+' | ' SET @Body=@Body+''+ISNULL(@AGEING,'')+' | ' SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW01,'')+' | ' SET @Body=@Body+''+ISNULL(@PENDING_FOR_OCR_DW02,'')+' | ' SET @Body=@Body+''+ISNULL(@NC01,'')+' | ' SET @Body=@Body+''+ISNULL(@NC02,'')+' | ' SET @Body=@Body+''+ISNULL(@TOTAL,0)+' | ' SET @Body=@Body+''+ISNULL(@RESPONSIBILITY,'')+' |
| *This is a system generated mail.Please donot reply to this. |
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 + "
";
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 + "
";
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 + "
";
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' );
}
}
| " + DtTruck.Rows[i - 1]["TRUCKTYPE"].ToString() + " |
| No Records Found... |
| " + DtTrans.Rows[i - 1]["TRANSNAME"].ToString() + " |
| No Records Found... |
| " + DtTruck.Rows[i - 1]["GTRK_TRUCK_NO"].ToString() + " |
| No Records Found... |
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::
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
Subscribe to:
Posts (Atom)