Some Sql Statements Tips

ex 1.
EXEC OPENDATASOURCE('SQLOLEDB','Data Source=sername;User ID=jerry;Password=password').master.dbo.xp_cmdShell 'C:\myprogram.exe "parameter1" "parameter2"'

if 64 bits, should use:
select * from
openquery(server, 'Select * from mydb.dbo.mytable')


ex 2.
If we want to convert an table to xml, using sql, there are simple method to do this things:

select * from customer for xml auto, elements, root('customer')

ex 3.
EXEC sp_addlinkedserver 'MyServer', N'SQL Server'

ex 4.
select column_name, * from information_schema.columns where table_name='Mytable'
select table_name from information_schema.tables
(include views)

ex 5.
SELECT [name] from [sys].[tables]
(not include views)

exe 6.
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
( EXEC sp_MSForEachTable 'DROP TABLE ?' this one need caution)

exe 7.
update table SET col = REPLACE(col, CHAR(9), ' ')
update table SET col = REPLACE(col, CHAR(13) + CHAR(10), ' ')
update table SET col = REPLACE(col, CHAR(10), ' ')

Silverlight+ADO.net Data Service (2)

Last time, we talk about how to build the ado.net data service server side development. There are plenty of posts on how to build the ado.net dat service server side application. Scott Gu's blog is one of the most classical one. For the client side, we are going to use a data grid as the component.

In client side, reference, add service reference.

Let's put in the page.xaml some code to place the buttons, data grid, text box et al, and you can design your own in Expression Blend to make the controls beautiful.

ex1. Get Data
private void ButtonSelect_Click(object sender, RoutedEventArgs e)
{
ServiceReference.DataEntities ws = new ServiceReference.DataEntities(new Uri("MyDataService.svc", UriKind.Relative));
var query = (from p in ws.CustomerSet select p);
DataServiceQuery userQuery = (DataServiceQuery)query;
userQuery.BeginExecute(new AsyncCallback(OnLoadComplete), query);
}
void OnLoadComplete(IAsyncResult result)
{
DataServiceQuery query = (DataServiceQuery)result.AsyncState;
dataGrid.ItemsSource = query.EndExecute(result).ToList();
}

The above example is show on my APM essay.

ex 2. Insert Data
We can follow msdn: http://msdn.microsoft.com/en-us/library/cc903944(VS.95).aspx to write code.
We drag four text box to the page.xaml, and an insert button. The code a like below:


//Insert Record by Text Box
private void InsertClick(object sender, RoutedEventArgs e)
{
ServiceReference.DataEntities ws = new ServiceReference.DataEntities(new Uri("MyDataService.svc", UriKind.Relative));
Customer ds = new Customer();

ds.ProductID = Int32.Parse(PIDText.Text);
ds.ProductName = PNText.Text;
ds.Price = PRText.Text;
ds.ShippingConfirmNumber = SNText.Text;
ws.AddToDataSources(ds);
ws.BeginSaveChanges(OnSaveCompleted, ws);

}

void OnSaveCompleted(IAsyncResult result)
{

ServiceReference.DataEntities wsd = (ServiceReference.DataEntities)result.AsyncState;
ObservableCollection obds = new ObservableCollection();
obds.Add(result.AsyncState as Customer);
wsd.EndSaveChanges(result);

}
For this code, there are new data type ObservableCollection, it is a dynamic data type coming after WPF. It allows us to do dynamic data binding. For more details, we can go to msdn help document for this data type.

ex 3. Update and Delete

//Update DataGrid Record
private void UpdateClick(object sender, RoutedEventArgs e)
{

DataEntities de = new DataEntities(new Uri("MyDataService.svc", UriKind.Relative));
Customerds = (Customer)dataGrid.SelectedItem;
de.AttachTo("CustomerSet", ds);
de.UpdateObject(ds);
de.BeginSaveChanges(OnUpdate, de);
}

void OnUpdate(IAsyncResult result)
{
ServiceReference.DataEntities wsd = (ServiceReference.DataEntities)result.AsyncState;
ObservableCollection obds = new ObservableCollection();
obds.Add(result.AsyncState as Customer);
wsd.EndSaveChanges(result);
}


//Delete DataGrid Record
private void DeleteClick(object sender, RoutedEventArgs e)
{
ServiceReference.DataEntities ws = new ServiceReference.DataEntities(new Uri("MyDataService.svc", UriKind.Relative));
Customer ds = (Customer)dataGrid.SelectedItem;
ws.AttachTo("CustomerSet", ds);
ws.DeleteObject(ds);
ws.BeginSaveChanges(OnUpdate, ws);
}
void OnDelete(IAsyncResult result)
{
ServiceReference.DataEntities wsd = (ServiceReference.DataEntities)result.AsyncState;
ObservableCollection obds = new ObservableCollection();
obds.Add(result.AsyncState as Customer);
wsd.EndSaveChanges(result);

}

Notice the msdn code does not work for the link: http://msdn.microsoft.com/en-us/library/cc903956(VS.95).aspx

There are 3 things the msdn code should be changed:
(1) We should use an AttachTo() method in BeginSaveChanges block. And attach our created data class to the data set.
(2) ws.BeginSaveChanges(OnUpdate, ws); The second parameter should be the service reference object or entity proxy object, what we updated here are the service reference, not the data class we created.
(3) In the EndSaveChanges block, we should use ObservableCollection, the dynamic collection data type.

Silverlight+ADO.net Data Service (1)

Firstly let's go from test page, there are numerous post how how to build a ado.net data service.

In order to use ado.net data service, you need to install many packages, for these, just go to the microsoft offical website to download and install them.


And we create tables in our database quickly, We just create customers, products, Shippings table.


Let's new a silverlight application project (you need to also install the silverlight 2 packages), and in server side add ado.net entity data model (call it mymodel.edmx, call the entity name: MyStoreEntities1). Add an ADO.net Data Service, called it MyDataService.svc.

The defaulted MyDataService class has the following code:

public class MyDataService : DataService< /* TODO: put your data source class name here */ >
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(IDataServiceConfiguration config)
{ // TODO: set rules to indicate which entity sets and service operations
//are visible, datable, etc.
// Examples:
// config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);
// config.SetServiceOperationAccessRule("MyServiceOperation",
//erviceOperationRights.All);
}
}

Change the code to be:

public class MyDataService : DataService<>
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(IDataServiceConfiguration config)
{ // TODO: set rules to indicate which entity sets and service operations
//are visible, datable, etc.
// Examples:
config.SetEntitySetAccessRule("*", EntitySetRights.All);
config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
}
}

We set all the rights to be all, and EntitySet, ServiceOperation to be "*", so that in client side we have right to deal with the data in server side.

And do one more thing, in MyModel.edmx designer changed the class Products to be Product, Customers to be Customer, so the in furture use, the entity data set name and entity class name will not be confused. Entity Data Set name will changed to CustomerSet, ProductSet and the Entity class be Customer and Product.

In this case the design of server side are finshed.

The Asynchronous Programming Model (APM)

"A synchronous method call waits for the method to complete before continuing with program flow, whereas an asynchronous method call will return immediately so that the program can perform other operations while the called method completes its work. "

The asynchronous design pattern used in the .NET Framework is characterized by the use of BeginXXXX and EndXXXX methods, where XXXX is the name of the synchronous version of the method.

For example when dealing with file stream, there is BeginRead/EndRead method:

ex 1.

byte[] buffer=new byte[100];
string filename=...;
FileStream strm=...;

//Make asynchrounous call
IAsyncResult result=strm.BeginRead(buffer,0,buffer.Lentgh,null,null);

//do some work here while you wait

//Calling EndRead will block until the Async work is complete
int numBytes=strm.EndRead(result);

strm.Close();

The BeginRead is similar to Read Method, but it returns an IAsyncResult instead of the number of bytes read. At the end of the operation, you will call the EndRead with the IAsyncResult object and it will return the byetes read.

There are 3 styles of models to do asynchrous call, wait-until-done model(ex 1), polling model( similar to wait-until-done model, except in the "main" thread, call IsCompleted property on IAsyncResult object to see if complete), and callback mode.

Let's look at Callback Model:

ex 2.

static byte[] buffer=new byte[100];

static void TestCallbackAPM()
{
string filename=...;
FileStream strm=...;

//Make asynchronous call
IAsyncResult result=strm.BeginRead(buffer,0,buffer.Length, new AsyncCallBack(CompleteRead), strm);
}

static void CompleteRead(IAsyncResult result)
{
FileStream strm=(FileStream)result.AsyncState;

//finished, we can call EndRead and it will return without blocking
int numBytes=strm.EndRead(result);

strm.Close();
}

In our silverlight and ADO.net data service learning, we have similar code, they are using the callback model:

ex 3.
//Get Data
private void ButtonSelect_Click(object sender, RoutedEventArgs e)
{
ServiceReference.DataEntities ws = new ServiceReference.DataEntities(new Uri("WebDataService.svc", UriKind.Relative));
var query = (from p in ws.DataSources select p);
DataServiceQuery userQuery = (DataServiceQuery)query;
userQuery.BeginExecute(new AsyncCallback(OnLoadComplete), query);
}
void OnLoadComplete(IAsyncResult result)
{
DataServiceQuery query = (DataServiceQuery)result.AsyncState;
dataGrid.ItemsSource = query.EndExecute(result).ToList();
}


//Insert Record by Text Box
private void InsertClick(object sender, RoutedEventArgs e)
{
ServiceReference.DataEntities ws = new ServiceReference.DataEntities(new Uri("WebDataService.svc", UriKind.Relative));
DataSources ds = new DataSources();
ds.ProductID = Int32.Parse(PIDText.Text);
ds.SourceID = SIDText.Text;
ds.SourceGroupID = SGIDText.Text;
ds.SourceName = SNText.Text;
ws.AddToDataSources(ds);
ws.BeginSaveChanges(OnSaveChangesCompleted, ws);
}

void OnSaveChangesCompleted(IAsyncResult result)
{
ServiceReference.DataEntities wsd = (ServiceReference.DataEntities)result.AsyncState;
ObservableCollection obds = new ObservableCollection();
obds.Add(result.AsyncState as DataSources);
wsd.EndSaveChanges(result);
}

Static class, static member and singleton pattern

static class can only have static memebers, and the static class cannot be instantiated.so when we have a class:

public static classA{};

ClassA ca=new ClassA() is not permitted.

static members, such as static field, static constructor, static property, static method, they blong to the class, even this class is not static and the class can be instantiated, these static members are not belong to the instance of this class.

For example, we have a non-static class:

public classB
{

//static field
public static int i;

//static property
private static int j;
public static int J
{
get
{
return j;
}
set
{
value=j;
}
}

//static method
public static void Method()

{
Console.WriteLine(...);
}

}

When we use these members, for example we can have the instance of ClassB
ClassB cb=new ClassB();
And when use these static members, we can only use:

//static field
ClassB.i;

//static property
ClassB.J;

//static method
ClassB.Method();

The following code will guarantee a singleton design pattern:

class Singleton
{
public static readonly Singleton instance=new Singleton();
private Singleton() {}
}

This code have 2 point: the constructor is private, and there only have one instance.

The code is equallent to the following code:

class Singleton
{
public static readonly Singleton instance;

//static constructor, the content is excute before the instance is claimed as static

static Singleton()
{
instance=new Singleton();
}

//private constructor guarantee the constructor are not able to use out side of the class
private Singleton(){}
}

When we use the instance of this class, we can only write as: Singleton.Instance.