I have search so many place but didn't find any best solution for Insert/Update/Delete/View Using Silverlight Oracle database so when i write this code i would like to share it with everyone who need simple insert/update/delete with oracle and silverlight.
So here i am going to explain you how to connect with Oracle using silverlight application.
1)First of all create DataBase here i am making Customer Table like this.
2)Create Procedure for Inserting and Deleting Detrails in table like this one
13)Come to your Main Silverlight Application that is SilverlightOracleConnection add the service reference first like this.
17)Save everything and build your Solution press ctrl+Shift+B.
So here i am going to explain you how to connect with Oracle using silverlight application.
1)First of all create DataBase here i am making Customer Table like this.
2)Create Procedure for Inserting and Deleting Detrails in table like this one
CREATE OR REPLACE Procedure UpdateCustomer(
Incustomerid in number,
Iccustomercode in char,
Iccustomername in char) AS
counter number;
BEGIN
select count(*)
into counter
from customer c
where c.customerid = Incustomerid;
if (counter != 0) then
update customer c
set c.customercode =
Iccustomercode, c.customername = Iccustomername
where c.customerid =
Incustomerid;
else
insert into customer c
(c.customerid,
c.customercode, c.customername)
values
(Incustomerid,
Iccustomercode, Iccustomername);
end if;
END;
CREATE OR REPLACE PROCEDURE
DELETECUSTOMERINFO
(
INCUSTOMERID in NUMBER
)
AS
BEGIN
DELETE FROM CUSTOMER C WHERE
C.CUSTOMERID=INCUSTOMERID;
COMMIT;
END;
3)Now Come to the Silverlight Create new Silverlight projects.
4)Create Structure that separate your business logic,Data Acces Layer,and UI like in three tier architecture.
5)I have Added TWO(2) Class liberary project like this
5) And ONE(1) Silver light Class liberary
6) The Structure will be looking like this see below
6) ok done with the structure great!!!!. Now look at we have ....
We have BLL
, DAL , Class liberary
We have Model
Silverlight Class Liberary
And SilverLight Application SilverlightOracleConnection
And
Web Project SilverlightOracleConnection.web
7)Now go to your Model
Silverlight Class Liberary add new class name it as Customer.cs and write the get set property like this
namespace Model
{
public class Customer
{
private int _customerid;
public int Customerid
{
get { return _customerid; }
set
{ _customerid = value; }
}
private string _customercode;
public string Customercode
{
get { return _customercode; }
set
{ _customercode = value; }
}
private string _customername;
public string Customername
{
get { return _customername; }
set
{ _customername = value; }
}
}
}
8)Now come to you DAL class liberary and add new class and name it as DbConnection.cs and write the connection code like
using System.Data.OracleClient;
using System.Configuration;
using System.Data;
namespace DAL
{
class DbConnection
{
OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["Connection"].ConnectionString);
OracleCommand cmd = new OracleCommand();
DataSet ds = new DataSet();
OracleDataAdapter ad;
public void openCon()
{
if (con.State == ConnectionState.Open)
con.Close();
else
con.Open();
}
public void closeCon()
{
con.Close();
}
public DataSet GetData(OracleCommand cmd)
{
con.Open();
cmd.Connection = con;
ad = new OracleDataAdapter(cmd);
ad.Fill(ds);
return ds;
}
public int SaveOrDeleteData(OracleCommand cmd)
{
con.Open();
cmd.Connection = con;
return cmd.ExecuteNonQuery();
}
}
}
9)In you DAL class liberary and add new class and name it as DALcustomer.cs and write the connection code like for insert/update details and get customer details
* Add reference of your model here
using System;
using System.Data.OracleClient;
using System.Data;
using System.Windows.Forms;
using Model;
namespace DAL
{
public class DALcustomer
{
#region
Update or insert data of customer
public int Updateorinsert(Customer cus)
{
int i=0;
try
{
DbConnection d = new DbConnection();
OracleCommand objCmd = new OracleCommand();
objCmd.CommandText = "UpdateCustomer";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add("Incustomerid", OracleType.Number).Value =
cus.Customerid;
objCmd.Parameters.Add("Iccustomercode", OracleType.Char).Value =
cus.Customercode;
objCmd.Parameters.Add("Iccustomername", OracleType.Char).Value =
cus.Customername;
return i = d.SaveData(objCmd);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return i;
}
}
#endregion
#region
Get Customer information
public DataSet GetCustomer()
{
DataSet ds;
try
{
ds = new DataSet();
DbConnection d = new DbConnection();
OracleCommand objCmd = new OracleCommand();
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "select * from customer";
return ds = d.GetData(objCmd);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return ds = null;
}
}
#endregion
#region Delete Customer Information
public int DeleteCustomer(int id)
{
int i = 0;
try
{
DbConnection d = new DbConnection();
OracleCommand objCmd = new OracleCommand();
objCmd.CommandText = "DELETECUSTOMERINFO";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add("INCUSTOMERID", OracleType.Number).Value = id;
return i = d.SaveOrDeleteData(objCmd);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return i ;
}
}
#endregion
}
}
10)Now go to your BLL create
class name it as BLLCustomerMaster.cs
And write the code as
follows
* Here you will get one doubt that is why BLL we can
use this DAL directly into our code behind we already discuss
Business logic layer provide interface between DAL and Application layer by
using this we can maintain consistency to our application.
using System;
using System.Collections.Generic;
using DAL;
using Model;
using System.Data;
using System.Linq;
namespace BLL
{
public class BLLCustomerMaster
{
public int Savecustomer(Customer c)
{
int i;
DALcustomer cus = new DALcustomer();
return i = cus.Updateorinsert(c);
}
public List<Customer> GetCustomer()
{
DataSet ds = new DataSet();
DALcustomer cus = new DALcustomer();
ds = cus.GetCustomer();
List<Customer> objmydata = (from DataRow dr in ds.Tables[0].Rows
select new Customer()
{
Customerid = Convert.ToInt32(dr["CUSTOMERID"]),
Customercode =Convert.ToString (dr["CUSTOMERCODE"]),
Customername =Convert.ToString(dr["CUSTOMERNAME"])
}).ToList<Customer>();
return objmydata;
}
public int deleteCustomer(int id)
{
int i;
DALcustomer cus = new DALcustomer();
return i = cus.DeleteCustomer(id);
}
}
}
11)Finally goto your SilverlightOracleConnection.Web right
click and create WCF service name it as
CustomerService.svc like this.
12) Reference your BLL
and and call service as below.
using System.ServiceModel;
using BLL;
using Model;
using System.Collections.Generic;
namespace SilverlightOracleConnection.Web
{
[ServiceContract]
public class CustomerService
{
[OperationContract]
public int SaveCustomer(Customer c)
{
int i;
BLLCustomerMaster cm = new BLLCustomerMaster();
return i = cm.Savecustomer(c);
}
[OperationContract]
public List<Customer> GetCustomers(Customer c)
{
BLLCustomerMaster bmc = new BLLCustomerMaster();
return bmc.GetCustomer();
}
[OperationContract]
public int DeletCustomer(int id)
{
int i;
BLLCustomerMaster cm = new BLLCustomerMaster();
return i = cm.deleteCustomer(id);
}
}
}
14)Discover WCF service like this.
15)Now in your Main project go to MainPage.xaml and write the design code for inserting customer details
and display in grid
*Here i have used DEVEXPRESS controls you can use the normal control which available by default.
<UserControl
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:dxlc="http://schemas.devexpress.com/winfx/2008/xaml/layoutcontrol" xmlns:dxe="http://schemas.devexpress.com/winfx/2008/xaml/editors" xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk" x:Class="SilverlightOracleConnection.MainPage"
mc:Ignorable="d"
d:DesignHeight="300" d:DesignWidth="400">
<dxlc:LayoutControl x:Name="lcRoot" >
<dxlc:LayoutItem >
<dxlc:LayoutGroup Header="LayoutGroup"
View="GroupBox" Orientation="Vertical">
<dxlc:LayoutItem Label="CustomerID">
<dxe:TextEdit x:Name="txtXustomerID" Text="{Binding Customerid, Mode=TwoWay }" />
</dxlc:LayoutItem>
<dxlc:LayoutItem Label="CustomerCode" x:Name="lblCustomerCode">
<dxe:TextEdit x:Name="txtCustomerCode" Text="{Binding Customercode, Mode=TwoWay }" />
</dxlc:LayoutItem>
<dxlc:LayoutItem Label="CustomerName" x:Name="lblCustomerName">
<dxe:TextEdit x:Name="txtCustomerName" Text="{Binding Customername, Mode=TwoWay }" />
</dxlc:LayoutItem>
<dxlc:LayoutGroup>
<dxlc:LayoutItem >
<Button Content="Submit" x:Name="submit" Click="submit_Click"/>
</dxlc:LayoutItem>
<dxlc:LayoutItem >
<Button Content="Delete" x:Name="Delete" Click="Delete_Click"/>
</dxlc:LayoutItem>
</dxlc:LayoutGroup>
<dxlc:LayoutItem/>
<dxg:GridControl Name="datagrid"
SelectedItemChanged="datagrid_SelectedItemChanged">
<dxg:GridControl.Columns>
<dxg:GridColumn Header="Customer
Id" Binding="{Binding Path=Customerid, Mode=OneWay}">
</dxg:GridColumn>
<dxg:GridColumn Header="Customer Code" Binding="{Binding Path=Customercode, Mode=OneWay}">
</dxg:GridColumn>
<dxg:GridColumn Header="Customer
Name" Binding="{Binding Path=Customername, Mode=OneWay}">
</dxg:GridColumn>
</dxg:GridControl.Columns>
</dxg:GridControl>
</dxlc:LayoutGroup>
</dxlc:LayoutItem>
</dxlc:LayoutControl>
</UserControl>
16)Now call your WCF service in your MainPage.xaml.cs page like this
using System;
using System.Windows;
using System.Windows.Controls;
using SilverlightOracleConnection.customerservice;
namespace SilverlightOracleConnection
{
public partial class MainPage : UserControl
{
#region
Contructor
customerservice.CustomerServiceClient
objclient;
public MainPage()
{
InitializeComponent();
this.Loaded += new RoutedEventHandler(MainPage_Loaded);
}
#endregion
#region
Page load
void MainPage_Loaded(object sender, RoutedEventArgs e)
{
GetCustomerInfo();
}
#endregion
#region
Save Customer Details
public void saveCustomerInfo()
{
Customer cs = new Customer();
cs.Customerid = Convert.ToInt32(txtXustomerID.Text);
cs.Customercode =
txtCustomerCode.Text;
cs.Customername =
txtCustomerName.Text;
objclient = new CustomerServiceClient();
objclient.SaveCustomerCompleted +=
objclient_SaveCustomerCompleted;
objclient.SaveCustomerAsync(cs);
}
void objclient_SaveCustomerCompleted(object sender, SaveCustomerCompletedEventArgs e)
{
if (e.Error != null)
{
MessageBox.Show("erroe");
}
else
{
int i = Convert.ToInt32(e.Result);
MessageBox.Show(i + " Rows
updated");
GetCustomerInfo();
}
}
private void submit_Click(object sender, RoutedEventArgs e)
{
txtXustomerID.DoValidate();
txtCustomerCode.DoValidate();
txtCustomerName.DoValidate();
saveCustomerInfo();
}
#endregion
#region
GetCustomer information
public void GetCustomerInfo()
{
Customer c = new Customer();
objclient = new CustomerServiceClient();
objclient.GetCustomersCompleted +=
objclient_GetCustomersCompleted;
objclient.GetCustomersAsync(c);
}
void objclient_GetCustomersCompleted(object sender, GetCustomersCompletedEventArgs e)
{
this.datagrid.ItemsSource = e.Result;
}
#endregion
#region Delete Customer Information
public void DeleteCustomerInfo()
{
cs = new Customer();
objclient = new CustomerServiceClient();
objclient.DeletCustomerAsync(Convert.ToInt32(
txtXustomerID.Text));
objclient.DeletCustomerCompleted +=
objclient_DeletCustomerCompleted;
}
void objclient_DeletCustomerCompleted(object sender, DeletCustomerCompletedEventArgs e)
{
GetCustomerInfo();
}
private void Delete_Click(object sender, RoutedEventArgs e)
{
DeleteCustomerInfo();
}
private void datagrid_SelectedItemChanged(object sender,
DevExpress.Xpf.Grid.SelectedItemChangedEventArgs e)
{
var abc = datagrid.GetFocusedRow() as Customer;
txtXustomerID.Text = Convert.ToString(abc.Customerid);
txtCustomerName.Text =
abc.Customername;
txtCustomerCode.Text = abc.Customercode;
}
#endregion
}
}
18)Now run the
application your application will look like this enter the details and click submit.
Thank You ......
No comments:
Post a Comment