Học-Học mãi-Học mãi mãi: 2013

Đây là 1 VD minh họa đơn giản việc kết nối MySQL trong C#. Để kết nối với các hệ CSDL khác bằng ADO.NET ( Microsoft SQL Server , Oracle, Microsoft acess) thì có khá nhiều giáo trình đề cập đến rồi nên mình sẽ không nói thêm nữa. Hi vọng bài viết này sẽ giúp ích cho các bạn.

Để kết nối với CSDL các bạn sử dụng file connector của MySQL tải tại đây

Quote http://www.mediafire.com/?twxjkyxwllm.

Tạo một CSDL bằng MySQL, các bạn có thể cài đặt trọn bộ webserver(trong đó có sẵn MySQL) , ở đây mình sẽ dùng gói xampp-win32 (Server:Localhost, Database:temp , user:root/pass=””).

Tạo project mới, sau đó vào Project/Add reference…. Và Add file MySQL.Data.DLL vừa mới tải về vào project.
Sử dụng lớp MySql.Data.MySqlClient:
using MySql.Data.MySqlClient;
Khai Báo một Connection String:
string MyConString = “SERVER=localhost;” +
“DATABASE=temp;” +
“UID=root;” +
“PASSWORD= ; ” ;

Dạng đầy đủ của Connection String:
Connection String : “Server=hostname;” + “Database=database;” “Port=3306;”+ “User ID=username;” + “Password=password;” + “Pooling=false”

Server :Hostname hoặc IP của MySQL server.
Database: tên của CSDL. (Vd này mình dặt là ‘temp’)
Port: TCP/IP Port để kết nối (mặc dịnh là 3306).
User ID: user (Vd:root).
Password: pass.
Pooling: YES hoặc TRUE để sử dụng kết nối lấy từ pool , NO hoặc FALSE kết nối không lấy từ pool(mặc dịnh là TRUE (YES)).

Connection – pooling là gì?
Trích dẫn:
Quote
Connection-pooling làm giảm đáng kể tổng phí liên hợp với việc tạo và hủy kết nối cơ sở dữliệu. Connection-pooling cũng cải thiện tính quy mô của các giải pháp bằng cách giảm số lượng kết nối đồng thời mà một cơ sở dữ liệu phải duy trì—đa số thường “ngồi không” suốt một phần đáng kể thuộc thời gian sống của chúng. Với connection-pooling, thay vì tạo và mở một đối tượng kết nối mới mỗi khi cần, bạn có thể lấy kết nối đã mở từ pool. Khi bạn đã hoàn tất việc sử dụng kết nối, thay vì đóng nó, bạn trả nó về cho pool và cho phép đoạn mã khác sử dụng nó.
Theo mặc định, SQL Server và Oracle Data Provider cung cấp chức năng connection-pooling. Một pool sẽ hiện diện đối với mỗi chuỗi kết nối do bạn chỉ định khi mở một kết nối mới. Mỗi khi bạn mở một kết nối mới với chuỗi kết nối đã được sử dụng qua, nó sẽ được lấy từ pool hiện có. Chỉ khi bạn chỉ định một chuỗi kết nối khác thì data-provider mới tạo một pool mới.

Bạn có thể điều khiển các đặc tính của pool bằng cách sử dụng các thiết lập trong chuỗi kết
nối .

Tạo kết nối:
Vd:

Quote
MySqlConnection con = new SqlConnection(MyConString);
MySqlCommand com = con.CreateCommand();
MySqlDataReader reader;

Nhập lệnh SQL:

Quote
com.CommandText = “select * from login”;

Mở kết nối:
vd:

Quote
con.Open();

Thực hiện lệnh:
vd:

Quote
reader = com.ExecuteReader();

Lấy kết quả trả về (ở đây mình sẽ sử dụng listbox) :

Quote
while (reader.Read())
{
string thisrow = “”;
for (int i = 0; i < reader.FieldCount; i++)
thisrow += reader.GetValue(i).ToString() + “,”;
listBox1.Items.Add(thisrow);
}

Code tham khảo:

Quote
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
string MyConString = “SERVER=localhost;” +
“DATABASE=temp;” +
“UID=root;” +
“PASSWORD= ; ” ;
MySqlConnection con = new MySqlConnection(MyConString);
MySqlCommand com = con.CreateCommand();
MySqlDataReader reader;
com.CommandText = “select * from login”;
con.Open();
reader = com.ExecuteReader();
while (reader.Read())
{
string thisrow = “”;
for (int i = 0; i < reader.FieldCount; i++)
thisrow += reader.GetValue(i).ToString() + “,”;
listBox1.Items.Add(thisrow);
}
con.Close();

}

Introduction:

The objective of this article is to create a WCF service that retrieves data from the database using LINQ to SQL classes and a Windows Phone 7 application that consumes that service to display the data.
The article contains three main parts:

Creating the database

Creating the WCF Service

Creating the Windows Phone 7 application that consumes the WCF service

The above mentioned scenario has been illustrated with an example. In the example I have created a table that contains some details about an employee (Employee ID, Employee Name and Phone No). When the user of our Windows Phone 7 application enters an employee id, he will be able to retrieve the details of that employee from the database through the WCF service.
1. Creating the Database:
The following steps have been followed to create the database

Now some data are added to the table.

Now we have created our database.

2. Creating the WCF Service
We have followed the steps given below to create the WCF Service.

Open the Visual Studio 2010 & create a new WCF Service Application. (In our case, the name of the WCF service is MyService)

Right Click on the project name and then add a new item.

Now add a LINQ to SQL class to the project.

Now go to server explorer and add a new data connection. (Right click on Data Connection and select Add Connection)

Give the server name, select the database and click test connection. Then click OK.

Now from server explorer select your database and table and drag the table to the middle pane.

Now open the IService1.cs and delete all the default codes. Write down the following code there. I have explained the code later.

Now open the Service1.svc.cs and delete all the default codes. Write down the following code there. I have explained the code later.

Right click on service1.svc and select the "view in browser" option.

Our service is running now (In Cassini server).

Copy the URL of the service.

3. Creating the Windows Phone 7 application that consumes the WCF service

Open the Microsoft Visual Studio 2010 Express for Windows Phone and create a Windows Phone Application. (In our case the name of the Windows Phone 7 application is MyClientWin7)

In MainPage.xaml drag and drop a TextBox and a Button as shown below.

The XAML code for MainPage.xaml is given below
<phoneNavigation:PhoneApplicationPage
x:Class="MyClientWin7.MainPage" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:phoneNavigation="clr- namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone.Controls.Navigation" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="800" FontFamily="{StaticResource PhoneFontFamilyNormal}" FontSize="{StaticResource PhoneFontSizeNormal}" Foreground="{StaticResource PhoneForegroundBrush}">
<Grid x:Name="LayoutRoot" Background="{StaticResource PhoneBackgroundBrush}"> <Grid.RowDefinitions> <RowDefinition Height="Auto"/> <RowDefinition Height="*"/> </Grid.RowDefinitions>
<Grid x:Name="TitleGrid" Grid.Row="0"> <TextBlock Text="MY APPLICATION" x:Name="textBlockPageTitle" Style="{StaticResource PhoneTextPageTitle1Style}"/> <TextBlock Text="page title" x:Name="textBlockListTitle" Style="{StaticResourcePhoneTextPageTitle2Style}"/> </Grid>
Right click on the project name (MyClientWin7) and add a new item. Then select a Windows Phone Portrait Page and add it to the project.
In Page1.xaml, drag and drop a list box.
The XAML code for Page1.xaml is given below
<navigation:PhoneApplicationPage
x:Class="MyClientWin7.Page1" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:navigation="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone.Controls.Navigation" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" SupportedOrientations="Portrait" mc:Ignorable="d" d:DesignHeight="800" d:DesignWidth="480">
</navigation:PhoneApplicationPage>
Now right click on the References and add a Service Reference.
In the Address paste the URL of the WCF service which is running and click Go. Then click OK.
Now open the MainPAge.xaml.cs (Double click on the button "Find") and write down the follwing code.
Open Page1.xaml.cs and write down the code.
listBox1.ItemsSource = e.Result;
} }
Replace the code for the ListBox in the Page1.xaml in the following way.
Rebuild the solution and start debugging. The following screen will appear in the emulator.
Enter some Employee ID in the textbox and click the Find button. (Here I have entered U22975 in the text box )The following screen will appear then showing the details of the employee whose ID was entered in the textbox.
using System;

using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Collections.Generic;
namespace MyService
{
[ServiceContract]
public interface IService1
{
[OperationContract]
List<MyEmployee> FindEmployee(string uid);
}
}
Explanation of the code :
The interface Iservice1 is the service contract of our WCF service. We have declared only one function (FindEmployee) as our operation contract. This function takes a string as an argument (which is the employee ID entered by the user) and return a List of MyEmployee which is our data model class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
namespace MyService
{
public class Service1 : IService1
{
public List<MyEmployee> FindEmployee(string uid)
{
DataClasses1DataContext context = new DataClasses1DataContext();
var res = from r in context.MyEmployees where r.EmpID == uid select r;
return res.ToList();
}
}
}
Explanation of the code :
The class Service1 is our service that implements the service contract IService1. In this class we have defined the operation contract FindEmployee. In this method, we have created a data context object. Then we have written a simple LINQ to SQL query that fetches the details of a particular employee whose employee id was passed as an argument of the operation contract. The method returns a list of objects of MyEmployee class. (We could have returned only one object of MyEmployee class also as we are fetching data using the primary key)
<Grid x:Name="ContentGrid" Grid.Row="1"> <TextBox Height="32" HorizontalAlignment="Left" Margin="40,87,0,0"Name="textBox1" Text="" VerticalAlignment="Top" Width="401" /> <Button Height="70" HorizontalAlignment="Left" Margin="152,304,0,0"Name="button1" VerticalAlignment="Top" Width="160" Content="Find" Click="button1_Click" /> </Grid> </Grid>
</phoneNavigation:PhoneApplicationPage>
<Grid x:Name="LayoutRoot" Background="{StaticResource PhoneBackgroundBrush}"> <Grid.RowDefinitions> <RowDefinition Height="170"/> <RowDefinition Height="*"/> </Grid.RowDefinitions>
<Grid Grid.Row="0" x:Name="TitleGrid"> <TextBlock x:Name="ApplicationName" Text="MY APPLICATION" Style="{StaticResource PhoneTextPageTitle1Style}"/> <TextBlock x:Name="ListName" Text="page title" Style="{StaticResourcePhoneTextPageTitle2Style}"/> </Grid>
<Grid Grid.Row="1" x:Name="ContentGrid"> <ListBox Height="444" HorizontalAlignment="Left" Margin="20,81,0,0" Name="listBox1"VerticalAlignment="Top" Width="434" /> </Grid> </Grid>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using Microsoft.Phone.Controls;
namespace MyClientWin7
{
public partial class MainPage : PhoneApplicationPage
{
public MainPage()
{
InitializeComponent();
SupportedOrientations = SupportedPageOrientation.Portrait |SupportedPageOrientation.Landscape;
}
private void button1_Click(object sender, RoutedEventArgs e)
{
string s = textBox1.Text;
this.Content = new Page1(s);
}
}
}
Explanation of the code:
In the button click event (button1_Click), we have stored the textbox entry in a string and move to a new page (Page1) . In the Page1 constructor, we have passed the textbox entry.
(Here we will find an error in new Page1(s) as the constructor defined in Page1.xaml.cs has no arguments. But we will change the constructor in the next step. Then the error will be removed.)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using Microsoft.Phone.Controls;
using MyClientWin7.ServiceReference1;
namespace MyClientWin7
{
public partial class Page1 : PhoneApplicationPage
{
public Page1(string s)
{
InitializeComponent();
Service1Client proxy = new Service1Client();
proxy.FindEmployeeCompleted += newEventHandler<FindEmployeeCompletedEventArgs>(proxy_FindEmployeeCompleted);
proxy.FindEmployeeAsync(s);
}
void proxy_FindEmployeeCompleted(object sender, FindEmployeeCompletedEventArgse)
{
}
Explanation of the code:
In the Page1 constructor, we have created a proxy object of the service. Now all WCF service calls from Silverlight are made through asynchronous communications. The FindEmployee contract is implemented in the generated proxy with an asynchronous method FindEmployeeAsync and an event proxy_FindEmployeeCompleted that is raised when the operation has completed.
The proxy_ FindEmployeeCompleted event sets the ItemSource property of the list box with the return value of the operation contract.
<ListBox Height="444" HorizontalAlignment="Left" Margin="20,81,0,0" Name="listBox1"VerticalAlignment="Top" Width="434" >
<ListBox.ItemTemplate>
<DataTemplate>
<StackPanel Orientation="Horizontal">
<TextBlock Text="{Binding EmpID}"/>
<TextBlock Text="{Binding EmpFirstName}"/>
<TextBlock Text="{Binding EmpLastName}"/>
<TextBlock Text=" " />
<TextBlock Text="{Binding PhoneNo}"/>
</StackPanel>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
Explanation of the code:
In this code we have overriden the ListBox control's ItemTemplate and supplied a custom DataTemplate. This DataTemplate uses one StackPanel to stack some textblocks together horizontally. These textblocks are used to bind to the data from the table in a readable manner.