New article on CodeProject.com: SMO Tutorial 2 of n - (Programming objects not used for data storage)

Before a couple of days I have posted new article on CodeProject.com. This is the second article from series of articles about Server Management Objects (SMO) and programming tasks using SMO.

• Introduction
• Background
• Logins
  
  • Enumerating SQL Server logins
  • Creating SQL Server login
• Users
  
  • Enumerating database users
  • Creating database users
  • Assigning role to database user
• Roles
  
  • Enumerating database roles
  • Creating new database role
• Server permissions
• .NET Framework Assemblies

How to shuffle array items using C#

Here is a simple code of method which shuffle array items:

static Random rng = new Random();

public void Randomize(IList list)
{

    for (int i = list.Count - 1; i > 0; i--)
    {
        int swapIndex = rng.Next(i + 1);
        if (swapIndex != i)
        {
            object tmp = list[swapIndex];
            list[swapIndex] = list[i];
            list[i] = tmp;
        }
    }
}

How to Grant, Deny and Revoke server pesmissions using SMO

SMO allows you to grant, deny and revoke server permissions to and from SQL Server login account. ServerPermissionInfo object captures the set of server permission returned by EnumServerPermissions() metod. You can pass name of SQL Server login as parameter to this method. Than you will get permissions for this login.

ServerPermissionSet object represent set of SQL Server permissions you want to grant, deny or revoke.

Server permissions are required when granting, denying, or revoking server-level permissions on an instance of SQL Server. The ServerPermission object is used to specify the set of permissions that apply to the Grant, Deny, and Revoke methods of the Server object. Also, server permissions can be added to the ServerPermissionSet object, which can also be used with the Deny, Revoke, and Grant methods.

ServerConnection conn = new ServerConnection(@"SQL_SERVER_INSTANCE", "LOGIN", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Database db = srv.Databases["AdventureWorks"];

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("LOGIN"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");

    ServerPermissionSet sps;
    sps = new ServerPermissionSet(ServerPermission.CreateAnyDatabase);
    srv.Grant(sps, "slimak");

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("LOGIN"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");

    sps = new ServerPermissionSet(ServerPermission.ViewAnyDatabase);
    srv.Deny(sps, "slimak");

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("LOGIN"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");

    sps = new ServerPermissionSet(ServerPermission.ViewAnyDatabase);
    srv.Revoke(sps, "slimak");

    foreach (ServerPermissionInfo serverPermInfo in srv.EnumServerPermissions("LOGIN"))
    {
        Console.WriteLine(serverPermInfo.ToString());
    }
    Console.WriteLine("----------------");
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

Calling JavaScript function from WinForms and vice-versa

I'be been working on application that needs to load page and display it in WebBrowser control. Second requirement was to allow interaction between WinForms application and web page. After a couple of minutes of googling I found some important information that helped me to solve my problem. Here I will show a sample application where JavaScript function is called from WinForms application.

Hole process can be described by following steps:

• Create Windows Forms application
• Add WebBrowser control into Form
• Create web page with JavaScript function you want to call

Calling JavaScript function from WinForms application isn't so difficult. WebBrowser constrol has property Document which gets an HtmlDocument representing the Web page currently displayed in the WebBrowser control.

You can use this property, in combination with the ObjectForScripting property, to implement two-way communication between a Web page displayed in the WebBrowser control and your application. Use the HtmlDocument.InvokeScript method to call script methods implemented in a Web page from your client application code. Your scripting code can access your application through the window.external object, which is a built-in DOM object provided for host access, and which maps to an object that you specify for the ObjectForScripting property.

<html>
     <head>
          <title></title>
          <script type="text/javascript">        

          function ShowMessage(message)
          {
               alert(message);
          }
        
          function ShowWinFormsMessage() {
               var msg = document.getElementById('txtMessage').value;
               return window.external.ShowMessage(msg);
          }
        
          </script>
     </head>  
     <body>
          <input type="text" id="txtMessage" />
          <input type="button" value="Show Message" onclick="ShowWinFormsMessage()" />
     </body>
</html>

Two JavaScript functions are in sample page. function ShowMessage(message) is called from WinForms application and function ShowWinFormsMessage call ShowMessage(msg) C# function.

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 System.Runtime.InteropServices;

namespace WebBrowserJavaScriptExample
{
    public partial class frmMain : Form
    {
        public frmMain()
        {
            InitializeComponent();
            webBrowser1.ObjectForScripting = new ScriptManager(this);
        }

        private void btnShowMessage_Click(object sender, EventArgs e)
        {
            object[] o = new object[1];
            o[0]=txtMessage.Text;
            object result = this.webBrowser1.Document.InvokeScript("ShowMessage", o);
        }

        private void frmMain_Load(object sender, EventArgs e)
        {
            this.webBrowser1.Navigate(@"E:\Projects\2010\WebBrowserJavaScriptExample\WebBrowserJavaScriptExample\TestPage.htm");
        }

        [ComVisible(true)]
        public class ScriptManager
        {
            frmMain _form;
            public ScriptManager(frmMain form)
            {
                _form = form;
            }

            public void ShowMessage(object obj)
            {
                MessageBox.Show(obj.ToString());
            }
        }
    }
}

More information about WebBrowser object ju can find here:

http://msdn.microsoft.com/en-us/library/system.windows.forms.webbrowser.document.aspx

New article on CodeProject.com: SMO Tutorial 1 of n - (Programming data storage objects)

Today I have posted new article on CodeProject.com. This is a first article from series of articles about Server Management Objects (SMO) and programming tasks using SMO.

First part of this series is about creating and managing databases storage objects. Here is a table of contents:

• Introduction
• Background
• Connecting to SQL Server
  
• Working with databses
  
  • Enumerating databases, filegroups and files
  • Enumerating database properties
  • Creating databases
  • Database backup
  • Database restore
• Tables
  
  • Enumerating tables and columns
  • Creating and removing tables
  • Checking table integrity
• Views
  
  • Enumerating Views
  • Creating and removing views
• Stored procedures
  
  • Enumerating stored procedures
  • Creating stored procedure
• Triggers
  
  • Creating DML Trigger
  • Creating DDL Trigger
• Schemas
  
  • Enumerating schemas
  • Creating schemas

How to export DataTable to CSV file

In this short post I will show you how to expert DataTable to CSV file. I have created C# function ExportTableToCsv with one parameted of DataTable data type.

private static string ExportTableToCsv(DataTable sourceTable)
{
    StringBuilder sb = new StringBuilder();

    for (int rowCount = 0; rowCount < sourceTable.Rows.Count; rowCount++)
    {
        for (int colCount = 0; colCount < sourceTable.Columns.Count; colCount++)
        {
            sb.Append(sourceTable.Rows[rowCount][colCount]);
            if (colCount != sourceTable.Columns.Count - 1)
            {
                sb.Append(",");
            }
        }
        if (rowCount != sourceTable.Rows.Count - 1)
        {
            sb.AppendLine();
        }
    }
    return sb.ToString();
}

SQL Server - How to create login using SMO

This is not a problem, when you have SQL Server Management studio installed. But what if you don’t have Management Studio is not installed? In this case you can use sqlcmd utility. In some cases, you need to backup databases programmatically and this could be done via Server Management objects. In this short example I will show you how to create SQL Server login and assign it to sysadmin server role.

ServerConnection conn = new ServerConnection("SQLSERVERINSTANCE", "USERNAME", "PASSWORD");
try
{
    Server srv = new Server(conn);
    Login newLogin = new Login(srv, "TestLogin");
    newLogin.LoginType = Microsoft.SqlServer.Management.Smo.LoginType.SqlLogin;
    newLogin.Create("TestPassword");
    newLogin.AddToRole("sysadmin");   
}
catch (Exception err)
{
    Console.WriteLine(err.Message);
}

This short example connects to SQL Server instance and creates new login called TestLogin with sysadmin server role.

Single layer perceptron as linear classifier

Perceptron is the simplest type of feed forward neural network. It was designed by Frank Rosenblatt as dichotomic classifier of two classes which are linearly separable. This means that the type of problems the network can solve must be linearly separable. Basic perceptron consists of 3 layers:

• sensor layer
• associative layer
• output neuron

There are a number of inputs (x_n) in sensor layer, weights (w_n) and an output. Sometimes w₀ is called bias and x₀ = +1/-1 (In this case is x₀=-1).


For every input on the perceptron (including bias), there is a corresponding weight. To calculate the output of the perceptron, every input is multiplied by its corresponding weight. Then weighted sum is computed of all inputs and fed it through a limiter function that evaluates the final output of the perceptron.
The output of neuron is formed by activation of the output neuron, which is function of input:

(1)

The activation function F can be linear so that we have a linear network, or nonlinear. In this example I decided to use threshold (signum) function:

(2)

Output of network in this case is either +1 or -1 depending on the input. If the total input (weighted sum of all inputs) is positive, then the pattern belongs to class +1, otherwise to class -1. Because of this behavior, we can use perceptron for classification tasks.

Lets consider we have a perceptron with 2 inputs and we want to separate input patterns into 2 classes. In this case the separation between the classes is straight line, given by equation:

(3)

When we set x₀=-1 and mark w₀=θ then we can rewrite equation (3) into form:

(4)

Here I will describe learning method for perceptron. Learning method of perceptron is iterative procedure that adjust the weights. A learning sample is presented to the network. For each weight the new value is computed by adding a correction to the old value. The threshold is updated in the same way:

(5)

where y is output of perceptron, d is desired output and γ is the learning parameter.

More about program and source code, you can find on www.CodeProject.com

How to replace Machine Name with Domain name in WCF Service wsdl link

Today I was developing the application for streaming files on server using WCF. Everything was fine until I deployed application on production server. I found out that WCF was taking machine name instead of IP address or domain name. This accurs when WCF service is hosted on IIS. The links to wsdl use local machine name. The autogenerated page I received when I was navigating to the .svc page said:

svcutil.exe http://[machinename]/FileTransferService.svc

I discovered that it could be done by folloving steps:

• Produce the wsdl in the browser and save to file (by hitting .svc?wsdl from browser)
• Produce the xsd files by hitting url from wsdl (xsd=xsd0, etc), and save to file from browser
• replace all machine name references from wsdl with domain name (or ip address) and change xsd references and save
• replace all machine name references from xsd files with domain name (or ip address)
• make sure to name xsd file with .xsd extension (ie, name0.xsd, name1.xsd, name2.xsd)
• copy wsdl and xsd file(s) to virtual directory
• add to your web.config following lines

<behaviors>
      <serviceBehaviors>
        <behavior name="MyServiceBehavior">
          <serviceMetadata httpGetEnabled="true" externalMetadataLocation="http://IPorDomainName/MyServices/FileTransferService.wsdl"  />
          <serviceDebug includeExceptionDetailInFaults="false" />
        </behavior>
      </serviceBehaviors>
    </behaviors>

New Article on CodeProject.com

Before couple of days I have posted a new article on CodeProject.com. In this article I will show you, how to backup and restore databases using SMO. More about that you can find here: http://www.codeproject.com/KB/database/SQLServer_Backup_SMO.aspx

SQL Server - How to create LinkedServer using SMO

LinkedServer allows you to access resources in the other resources. Consider, you have a server named "ServerA" and you would like to grab some data from the remote server named "ServerB". In that case, you need to create a linked server. In our scenario, the linked server is "ServerB".

The linked servers communicate using an OLE DB connection. These connections handle the commands between the linked servers, so coders and administrators can query data and tables without knowing the intricacies of database communication.

Here is the code example that shows how to create LinkedServer using SMO:

Server srv = new Server(@"INSTANCEA");
LinkedServer lsrv = new LinkedServer(srv, "INSTANCEB"); 
LinkedServerLogin login = new LinkedServerLogin();
login.Parent = lsrv;
login.Name = "LOGIN_ON_INSTANCEA";
login.RemoteUser = "LOGIN_ON_INSTANCEB";
login.SetRemotePassword("PASSWORD");
login.Impersonate = false;
lsrv.ProductName = "SQL Server";
lsrv.Create();
login.Create();

By specifying SQL Server as the product name, data is accessed on the linked server by using the SQL Server Client OLE DB Provider, which is the official OLE DB provider for SQL Server.