public static mtVendor GetVendor(string vendorCode) { string sSQL = "SELECT * FROM dbo.mt_vendor WHERE vendorCode=@vendorCode"; using (SqlConnection cn = new SqlConnection(Config.ConnectionString)) { cn.Open(); SqlCommand cmd = new SqlCommand(sSQL, cn); cmd.Parameters.Add("@vendorCode", SqlDbType.VarChar).Value = vendorCode; SqlDataReader rs = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (!rs.Read()) { return(null); } mtVendor v = new mtVendor(); v.vendorCode = rs["vendorCode"].ToString(); v.vendorName = rs["vendorName"].ToString(); v.phoneArea = rs["phonearea"] + ""; v.phonePrefix = rs["phoneprefix"] + ""; v.phoneSuffix = rs["phonesuffix"] + ""; v.ext = rs["ext"] + ""; v.RID = Convert.ToInt32(rs["RID"]); return(v); } }
public static void Insert(mtVendor v) { string sSQL = @"INSERT INTO dbo.mt_vendor (vendorCode, vendorName, phoneArea, phonePrefix, phoneSuffix, ext) VALUES (@vendorCode, @vendorName, @phoneArea, @phonePrefix, @phoneSuffix, @ext)"; using (SqlConnection cn = new SqlConnection(Config.ConnectionString)) { cn.Open(); SqlCommand cmd = new SqlCommand(sSQL, cn); cmd.Parameters.Add("@vendorCode", SqlDbType.VarChar, 10).Value = v.vendorCode; cmd.Parameters.Add("@vendorName", SqlDbType.VarChar, 50).Value = v.vendorName; cmd.Parameters.Add("@phoneArea", SqlDbType.VarChar, 3).Value = v.phoneArea; cmd.Parameters.Add("@phonePrefix", SqlDbType.VarChar, 3).Value = v.phonePrefix; cmd.Parameters.Add("@phoneSuffix", SqlDbType.VarChar, 4).Value = v.phoneSuffix; cmd.Parameters.Add("@ext", SqlDbType.VarChar, 10).Value = v.ext; cmd.ExecuteNonQuery(); } }
//public static void Update(mtVendor v) //{ // string sSQL = @"IF EXISTS(SELECT * FROM dbo.mt_vendor WHERE vendorCode=@vendorCode and VGroupCode = @VGroupCode) // UPDATE dbo.mt_vendor SET vendorName=@vendorName, phoneArea=@phoneArea, phoneprefix=@phonePrefix, // phoneSuffix=@phoneSuffix, ext=@ext, VGroupCode = @VGroupCode WHERE vendorCode = @vendorCode // ELSE INSERT INTO dbo.mt_vendor (vendorCode, vendorName, phoneArea, phonePrefix, phoneSuffix, ext, VGroupCode) // VALUES (@vendorCode, @vendorName, @phoneArea, @phonePrefix, @phoneSuffix, @ext, @VGroupCode)"; // using (SqlConnection cn = new SqlConnection(Config.ConnectionString)) // { // cn.Open(); // SqlCommand cmd = new SqlCommand(sSQL, cn); // cmd.Parameters.Add("@vendorCode", SqlDbType.VarChar, 10).Value = v.vendorCode; // cmd.Parameters.Add("@vendorName", SqlDbType.VarChar, 50).Value = v.vendorName; // cmd.Parameters.Add("@phoneArea", SqlDbType.VarChar, 3).Value = v.phoneArea; // cmd.Parameters.Add("@phonePrefix", SqlDbType.VarChar, 3).Value = v.phonePrefix; // cmd.Parameters.Add("@phoneSuffix", SqlDbType.VarChar, 4).Value = v.phoneSuffix; // cmd.Parameters.Add("@ext", SqlDbType.VarChar, 10).Value = v.ext; // cmd.Parameters.Add("@VGroupCode", SqlDbType.VarChar, 20).Value = v.vGroupCode; // cmd.ExecuteNonQuery(); // } //} public static void Update(mtVendor v) { string sSQL = @"UPDATE dbo.mt_vendor SET vendorName=@vendorName, phoneArea=@phoneArea, phoneprefix=@phonePrefix, phoneSuffix=@phoneSuffix, ext=@ext, vendorCode=@vendorCode WHERE RID = @RID"; using (SqlConnection cn = new SqlConnection(Config.ConnectionString)) { cn.Open(); SqlCommand cmd = new SqlCommand(sSQL, cn); cmd.Parameters.Add("@vendorCode", SqlDbType.VarChar, 10).Value = v.vendorCode; cmd.Parameters.Add("@vendorName", SqlDbType.VarChar, 50).Value = v.vendorName; cmd.Parameters.Add("@phoneArea", SqlDbType.VarChar, 3).Value = v.phoneArea; cmd.Parameters.Add("@phonePrefix", SqlDbType.VarChar, 3).Value = v.phonePrefix; cmd.Parameters.Add("@phoneSuffix", SqlDbType.VarChar, 4).Value = v.phoneSuffix; cmd.Parameters.Add("@ext", SqlDbType.VarChar, 10).Value = v.ext; cmd.Parameters.Add("@RID", SqlDbType.Int).Value = v.RID; cmd.ExecuteNonQuery(); } }