The easiest way to upload a database to dotster is to use the sql hosting toolkit.   Visual studio 2008 installs the sql hosting toolkit for you otherwise you need to download and install from the link. 

 

Steps to do this

1) Create a database in the dotster control panel.

2) In the server explorer create a link to the database you want to upload. 

3) Right click on the database and select publish to provider in the wizard make sure you select sql 2000 as the target database schema.

4) On the codeplex website they use to have a webpage available to use to help run the script to create your database.  Now they have a webservice for this.   I kind of think the webservice is over kill to just publish 1 database.  So create a c# website which targets the .net framework 2.0

5) Add the script file you created in step 3 to the website.

6) Add the following code to webpage

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Web;
using System.IO;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

namespace WebApplication3
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string fileName = Server.MapPath(@"<<YOUR_SCRIPTFILE>>.SQL");

            // Connection string to the server you want to execute against
            string connectionString = @"Server=<<YOUR_SERVER>>;User ID=<<YOUR_USERNAME>>;Password=<<YOUR_PASSWORD>>;Initial Catalog=<<YOUR_DATABASE>>";

            // Timeout of batches (in seconds)
            int timeout = 600;

            SqlConnection conn = null;
            try
            {
                Response.Write(String.Format("Opening file {0}<BR>", fileName));

                // read file
                using (StreamReader sr = new StreamReader(new FileStream(fileName, FileMode.Open)))
                {
                    Response.Write("Connecting to SQL Server database...<BR>");

                    // Create new connection to database
                    conn = new SqlConnection(connectionString);

                    conn.Open();

                    while (!sr.EndOfStream)
                    {
                        StringBuilder sb = new StringBuilder();
                        SqlCommand cmd = conn.CreateCommand();

                        while (!sr.EndOfStream)
                        {
                            string s = sr.ReadLine();
                            if (s != null && s.ToUpper().Trim().Equals("GO"))
                            {
                                break;
                            }

                            sb.AppendLine(s);
                        }

                        // Execute T-SQL against the target database
                        try
                        {
                            cmd.CommandText = sb.ToString();
                            cmd.CommandTimeout = timeout;

                            cmd.ExecuteNonQuery();

                        }
                        catch (Exception ex)
                        {
                            Response.Write(sb.ToString().Replace("\n", @"<br/>"));
                            Response.Write(ex.Message.ToString() + @"<br />");
                        }
                    }

                }
                Response.Write("T-SQL file executed successfully");
            }
            catch (Exception ex)
            {
                Response.Write(String.Format("An error occured: {0}", ex.ToString()));
            }
            finally
            {
                // Close out the connection
                //
                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                    catch (Exception e1)
                    {
                        Response.Write(String.Format(@"Could not close the connection.  Error was {0}", e1.ToString()));
                    }
                }
            }

        }
    }
}

 

Uploasd the website to dotster and open the webpage and your database should be copied to the new database.  Make sure you delete the website after you created the database to prevent someone from accidently undo changes to the database after you uploaded it.

Change the DataType of a Column

Sometimes when you fill a DataTable the .Net framework does not get the data type right.  Unfortunately once you fill a data table you can not change the data type.  You can use the data adapters FillScheme method to setup the data table this will allow you to be to change the data type.  Then you can fill the datatable with the data of the right type. .

VB Example
Dim conn As New SqlClient.SqlConnection("Server = .\sqlexpress; Database = NorthWind; " & _

"Integrated Security = sspi;")

Dim dt As New DataTable

Dim da As New SqlClient.SqlDataAdapter("Select * from [Order Details]", conn)

da.FillSchema(dt, SchemaType.Mapped)

dt.Columns("OrderID").DataType = GetType(Integer)

da.Fill(dt)

C# example

            SqlConnection  conn = new SqlConnection(
                 @"Server = .\sqlexpress; Database = NorthWind;Integrated Security  = sspi;");
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter("Select * from [Order Details]", conn);
            da.FillSchema(dt, SchemaType.Mapped);
            dt.Columns["OrderID"].DataType = typeof(int);
            da.Fill(dt);

Draw an Icon in a DataGridViewButtonCell

Here is a simple example on how to draw in a DataGridViewButtonCell. In this example an icon is displayed when button is pushed and the icon is made invisible when its pushed again. I am storing the if the button has been pressed in the cell's tag. I use the cellPainting event to draw the icon when needed.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

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

        private void Form1_Load(object sender, EventArgs e)
        {
            String strConn = "Server = .\\SqlExpress;Database = Pubs;Integrated Security = SSPI;";
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(strConn);
            SqlDataAdapter da = new SqlDataAdapter("Select * from titles", conn);
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            DataGridViewButtonColumn bc = new DataGridViewButtonColumn();
            bc.Tag = false;
            dataGridView1.Columns.Insert(0, bc);
        }

        private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (e.ColumnIndex == 0)
            {
                e.Value = "Repair";
                e.FormattingApplied = true;
            }
        }

        private void dataGridView1_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            if (e.ColumnIndex == 0 && e.RowIndex >= 0)
            {
                e.Paint(e.CellBounds, DataGridViewPaintParts.All);
                DataGridViewButtonCell bc = dataGridView1[0, e.RowIndex] as DataGridViewButtonCell;
                bool x;
                if (bc.Tag == null)
                {
                    x = false;
                }
                else
                {
                    x = (bool)bc.Tag;
                }
                if (x)
                {
                    Icon ico = new Icon("repair.ico");
                    e.Graphics.DrawIcon(ico, e.CellBounds.Left+3, e.CellBounds.Top+3 );
                }
                e.Handled = true;
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0 && e.RowIndex >= 0)
            {
                DataGridViewButtonCell bc = dataGridView1[e.ColumnIndex, e.RowIndex] as DataGridViewButtonCell;
                if (bc.Tag == null)
                {
                    bc.Tag = true;
                }
                else
                {
                    bc.Tag = !(bool)bc.Tag;
                }
            }
        }
    }
}

 

Data From Multiple Tables in a DataGridView

There is Msdn Knowledge base article which shows how to create a JoinView class. The JoinView class is for joining 2 tables together for data binding. Basically you load 2 or more tables into a dataset and set up some data relations for the related tables. When you create the Joinview the first argument is the main table, 2nd is a list of fields you want to show, 3rd is a filter, and 4th is the field to sort on. The last 2 arguments are option. Since the article includes a vb sample I am posting a c# example. I would recommend compiling JoinView.VB into a class so you use it with c#. I named my dll MSDNClasses.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MSDNClasses;

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

        DataSet ds = new DataSet();
        JoinView jv;

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(@"server=.\sqlexpress;integrated security=true;database=northwind");
            SqlDataAdapter daCust = new SqlDataAdapter(@"Select * From Customers", conn);
            SqlDataAdapter daEmp = new SqlDataAdapter(@"Select * From Employees", conn);
            SqlDataAdapter daOrd = new SqlDataAdapter(@"Select * From Orders", conn);

            daCust.Fill(ds, "Cust");
            daEmp.Fill(ds, "Emp");
            daOrd.Fill(ds, "Ord");

            // Create some data relations for the joinview to use

            ds.Relations.Add("CustOrd", ds.Tables["Cust"].Columns["CustomerID"], ds.Tables["Ord"].Columns["CustomerID"]);
            ds.Relations.Add("EmpOrd", ds.Tables["Emp"].Columns["EmployeeID"], ds.Tables["Ord"].Columns["EmployeeID"]);

            // Create Join View
            // Select fields for JoinView,  a filter to use, and Column to sort on

            jv = new JoinView(ds.Tables["Ord"],
                "OrderID,CustomerID,EmployeeID,OrderDate,CustOrd.CompanyName Company,CustOrd.ContactName Contact,CustOrd.ContactTitle Position,EmpOrd.FirstName,EmpOrd.LastName",
                "", "");

            dataGridView1.DataSource = jv;

        }
    }
}

Printer Compatibility Library 1.0

Visual Basic 6.0 and earlier had a printer object which made it simple to print. The Printer Compatibility Library 1.0 makes it possible to use the same object with VB or C# 2005. After installing the Power Pack just add a reference to Microsoft.VisualBasic.PowerPacks.Printing.Printer

 

VB Sample

Imports Microsoft.VisualBasic.PowerPacks.Printing.Compatibility.VB6

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim p As New Printer
        p.Print("Page 1")
        p.NewPage()
        p.Print("Page 2")
        p.EndDoc()
    End Sub
End Class

 

C# Sample

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.VisualBasic.PowerPacks.Printing.Compatibility.VB6;

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

        private void Form1_Load(object sender, EventArgs e)
        {
            Printer p = new Printer();
            p.Print("Page 1");
            p.NewPage();
            p.Print("Page 2");
            p.EndDoc();
        }
    }
}
 

Datagrid Validation

The DataGridView has a CellValidating Event to validate the data entered. Here is how to do it with the DataGrid.

There are 2 method to do this. My method is to add a tablestyle to the datagrid. For the cells I want to validate I handle the DataGridTextBoxColumn's Textbox's Validate event. George Shepherd's method is use the CurrentCellChanged event. You can read about this method in the Windows Forms FAQ.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

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

        private void Form1_Load(object sender, EventArgs e)
        {
            String strConn = "Server = .\\SqlExpress;Database = Pubs;Integrated Security = SSPI;";
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(strConn);
            SqlDataAdapter da = new SqlDataAdapter("Select Title, price from titles", conn);
            da.Fill(dt);
            dataGrid1.DataSource = dt;
            CurrencyManager cm = this.BindingContext[dt] as CurrencyManager;
            PropertyDescriptor pd = cm.GetItemProperties()["price"];

            DataGridTableStyle ts = new DataGridTableStyle();
            ts.MappingName = dt.TableName;

            DataGridTextBoxColumn tcTitle = new DataGridTextBoxColumn();
            tcTitle.MappingName = "Title";
            tcTitle.HeaderText = "Title";
            tcTitle.Width = 250;
            DataGridTextBoxColumn tcPrice = new DataGridTextBoxColumn(pd, "c2");
            tcPrice.MappingName = "price";
            tcPrice.HeaderText = "Book Price";
            tcPrice.Width = 100;
            tcPrice.TextBox.Validating += new CancelEventHandler(TextBox_Validating);
            ts.GridColumnStyles.Add(tcTitle);
            ts.GridColumnStyles.Add(tcPrice);

            dataGrid1.TableStyles.Add(ts);
        }

        void TextBox_Validating(Object sender, CancelEventArgs e)
        {
            TextBox txt = (TextBox)sender;
            txt.Text = txt.Text.Replace("$", "");
        }

    }
}

You will find a VB example on the VB-Tips Website

AutoComplete in DataGridView

In the datagridview's editing control showing event you have better access to the textboxes properties. Here is an example of adding autocomplete to the textbox.

VB Sample

Imports System.Data.SqlClient

Public Class Form1

    Dim scAutoComplete As New AutoCompleteStringCollection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConn As String
        Dim da As SqlDataAdapter
        Dim conn As SqlConnection
        Dim ds As New DataSet
        strConn = "Server = .;Database = NorthWind; Integrated Security = SSPI;"
        conn = New SqlConnection(strConn)
        da = New SqlDataAdapter("Select * from [Orders]", conn)
        da.Fill(ds, "Orders")
        DataGridView1.DataSource = ds.Tables("Orders")

        Dim cmd As New SqlCommand("Select CustomerID From customers", conn)
        Dim dr As SqlDataReader

        conn.Open()
        dr = cmd.ExecuteReader
        Do While dr.Read
            scAutoComplete.Add(dr.GetString(0))
        Loop
        conn.Close()
    End Sub

    Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        If DataGridView1.CurrentCell.ColumnIndex = 1 AndAlso TypeOf e.Control Is TextBox Then
            With DirectCast(e.Control, TextBox)
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.CustomSource
                .AutoCompleteCustomSource = scAutoComplete
            End With
        End If
    End Sub
End Class

 

C# Sample

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

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

        AutoCompleteStringCollection scAutoComplete = new AutoCompleteStringCollection();

        private void Form1_Load(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            String strConn = "Server = .;Database = NorthWind; Integrated Security = SSPI;";
            SqlConnection conn = new SqlConnection(strConn);
            SqlDataAdapter da = new SqlDataAdapter("Select * from [Orders]", conn);
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            SqlCommand cmd = new SqlCommand("Select CustomerID From customers", conn);
            SqlDataReader dr;
                conn.Open();
                dr=cmd.ExecuteReader();
                while(dr.Read())
                {
                    scAutoComplete.Add(dr.GetString(0));
                }
                conn.Close();
        }

        private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
        {
            if (dataGridView1.CurrentCellAddress.X == 1)
            {
                TextBox txt = e.Control as TextBox;
                txt.AutoCompleteCustomSource = scAutoComplete;
                txt.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
                txt.AutoCompleteSource = AutoCompleteSource.CustomSource;
            }
        }
    }
}

.Net Framework 3.0 Text to Speech

The dot net framework 3.0 now has a managed provider for text to speech. I tested this app on a machine with windows xp service pack 2 and the Dot Net FrameWork 3.0 RC1. The link is to set up instructions for the .net framework 3.0

 

For this sample add a reference to system.speech, place a textbox named txtSay, a button named btnSay, and listbox named lstVoice. The application fills a list box with the installed voices on the system at startup. When you click on the button it says the text in the textbox with the selected voice.

VB Sample

Imports System.Speech.Synthesis

Public Class Form1

    Private Sub btnSay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSay.Click
        Dim spk As New SpeechSynthesizer
        spk.SelectVoice(lstVoice.SelectedItem.ToString)
        spk.Speak(txtSay.Text)
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim spk As New SpeechSynthesizer
        For Each voice As InstalledVoice In spk.GetInstalledVoices
            lstVoice.Items.Add(voice.VoiceInfo.Name)
        Next
        lstVoice.SelectedIndex = 0
        txtSay.Text = "Hello World!"
    End Sub
End Class

 

C# Sample

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Speech.Synthesis;

namespace CS3._Speech
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SpeechSynthesizer spk = new SpeechSynthesizer();
            foreach(InstalledVoice voice in spk.GetInstalledVoices())
            {
                lstVoice.Items.Add(voice.VoiceInfo.Name);
            }
            lstVoice.SelectedIndex = 0;
            txtSay.Text = "Hello World";
        }

        private void btnSay_Click(object sender, EventArgs e)
        {
            SpeechSynthesizer spk = new SpeechSynthesizer();

            spk.SelectVoice(lstVoice.SelectedItem.ToString());
            spk.Speak(txtSay.Text);
        }
    }
}

Secure Strings

The SecureString is a new class that was added in the .Net framework 2.0 which allows you to store info in memory securely. The SecureString could be used to safely secure a password or credit number. The example shows how to add info to the string, prevent changes from being make to the data, and finally how to get the info back. I included c# and VB samples.

C# sample

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Security;
using System.Runtime.InteropServices;

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

        private void Form1_Load(object sender, EventArgs e)
        {
            //
            // How to add to a secure string
            //
            SecureString ss = new SecureString();
            foreach (Char c in "This is some info I need to keep secure".ToCharArray())
            {
                ss.AppendChar(c);
            }
            //
            // Prevent changes
            //
            ss.MakeReadOnly();

            //
            // How to get the info back
            //

            IntPtr ptr;
            ptr=Marshal.SecureStringToBSTR(ss);

            String s;
            s = Marshal.PtrToStringAuto(ptr);
            Marshal.ZeroFreeBSTR(ptr);

        }
    }
}

VB sample

Imports System.Security
Imports System.Runtime.InteropServices

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        '
        ' how to add to a secure string
        '
        Dim ss As New SecureString
        For Each c As Char In "This is some info I need to keep secure".ToCharArray
            ss.AppendChar(c)
        Next

        ' prevent changes

        ss.MakeReadOnly()

        '
        ' how to get the info back
        '
        Dim ptr As IntPtr
        ptr = Marshal.SecureStringToBSTR(ss)
        Dim s As String

        s = Marshal.PtrToStringAuto(ptr)
        Marshal.ZeroFreeBSTR(ptr)

    End Sub
End Class

 

Create a Pivot Table

Here is a quick example on how to rotate or pivot the data in a datagridview. This is a c# program which creates a new data table with a rotated version of the data to bind to. There is a VB version on the vb-tips website.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Pivot
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        DataTable dt1;

        private void Form1_Load(object sender, EventArgs e)
        {
            dt1 = CreateTable();
            DataTable dt2 = new DataTable("Reflected");
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                dt2.Columns.Add(i.ToString());
            }

            for (int x = 0; x < dt1.Columns.Count; x++)
            {
                DataRow dr = dt2.NewRow();
                for (int y = 0; y < dt1.Rows.Count; y++)
                {
                    dr[y] = dt1.Rows[y][x];
                }
                dt2.Rows.Add(dr);
            }

            dataGridView1.RowHeadersWidth = 60;
            dataGridView1.DataSource = dt2;
            dataGridView1.AllowUserToAddRows = false;
        }

        public DataTable CreateTable()
        {
            DataTable dt = new DataTable("Orginal");
            dt.Columns.Add("Name");
            dt.Columns.Add("State");
            dt.Columns.Add("Country");

            Object[] arRow = new Object[3];
            arRow[0] = "Ken";
            arRow[1]="Florida";
            arRow[2] = "US";

            dt.LoadDataRow(arRow,true);

            arRow[0] = "Cor";
            arRow[1] = "Holland";
            arRow[2] = "EU";

            dt.LoadDataRow(arRow, true);
            return dt;
        }

        private void dataGridView1_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            if (e.ColumnIndex == -1 && e.RowIndex >= 0)
            {
                StringFormat sf = new StringFormat();
                sf.Alignment = StringAlignment.Center;
                sf.LineAlignment = StringAlignment.Center;
                e.PaintBackground(e.ClipBounds, true);
                e.Graphics.DrawString(dt1.Columns[e.RowIndex].ColumnName.ToString(), this.Font, Brushes.Black, e.CellBounds, sf );
                e.Handled = true;
            }
        }

    }

}