MS SQL Restore and Backup code using .NET

First add a reference of Microsoft SQLDMO Object Library from COM tab .Then Write the below code
//Function for Restore

private void Restore()
{
try
{
this.Cursor = Cursors.WaitCursor;
//create an instance of a server class
SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
//connect to the server
srv.Connect("(local)","sa",""); //srv.Connect(servername,userid,password)
//create a restore class instance

SQLDMO.Restore res = new SQLDMO.RestoreClass();
//set the backup device = files property ( easy way )
res.Devices = res.Files;
//set the files property to the File Name text box
res.Files =textBox1.Text;//give full path with restore filename
//set the database to the chosen database
res.Database = "databaseName";
// Restore the database

res.ReplaceDatabase = true;
res.SQLRestore(srv);

MessageBox.Show("Database restored successfully.", "Restore Successfull");
this.Cursor = Cursors.Default;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message,"Error");
}
}


//Function for Backup

private void BackUp()
{
try
{
Class1.con.Close();
this.Cursor = Cursors.WaitCursor;
//create an instance of a server class
SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
//connect to the server

srv.Connect("(local)","sa",""); //srv.Connect(servername,userid,password)
//create a backup class instance
SQLDMO.Backup bak = new SQLDMO.BackupClass();
//set the backup device = files property ( easy way )
bak.Devices = bak.Files;
//set the files property to the File Name text box
//
bak.Files = this.textBox1.Text;//give full path with bakup filename

//set the database to the chosen database
bak.Database = "databasename";
//perform the backup
bak.SQLBackup(srv);
MessageBox.Show("Database successfully backed up.", "Backup Successfull");
this.Cursor = Cursors.Default;
}
catch(Exception err)
{
this.Cursor = Cursors.Default;
MessageBox.Show(err.Message,"Error");
}
}

Using Date Field in .NET when using with MS Access

Use #"+dateTimePicker1.Values+"# instead of '"+dateTimePicker1.Values+"'

This type is only used when retriving values using date or updating using date or deleting using date

NOTE: This type is not used while inserting datas to table

Inserting single quotes to a string in sql query

Just Add one more single quotes to that string by the user

eg: insert into table name values('D''zousa')

or

The programmer has to maintain that one using coding
Means we have to check the string if a single quotes exists or not.
If exists we have to add one more single quotes to that string.s

GETTING VALUES FROM DATASET

GETTING VALUES FROM DATASET

txtName.text = ds.Tables(0).Rows(row).Item(1)

Making Changes to Dataset Manually

ds.Tables(0).Rows(row).Item(1) = dt1.Value.ToShortTimeString()
ds.Tables(0).Rows(row).Item(2) = dt2.Value.ToShortTimeString()
ds.Tables(0).Rows(row).Item(3) = dt3.Text
ds.Tables(0).Rows(row).Item(4) = dt4.Text
ds.Tables(0).Rows(row).Item(5) = fin.Hours
ds.Tables(0).Rows(row).Item(6) = fin.Minutes


This is one method.There are so many other methods also; by using DataRow and DataTable etc..

CODE FOR POPULATING COMBOBOX USING DATASET

Dim ds as new Dataset
Dim sda as new OleDBDataAdapter("write query here",connection)

sda.Fill(ds,"tablename")

With cmbEOS
.DataSource = ds.Tables("Tablename")
.ValueMember = "fieldname
End With

DATAGRIDVIEW MOVEMENT THROUGH COLUMN WHEN PRESSING ENTER KEY

USING VB .NET

'Declare row,col as public

Dim row, col As Integer


'Write the below code in CellEndEdit Event

Private Sub DataGridView1_CellEndEdit(ByVal sender As System.Object, ByVal e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit

row = e.RowIndex
col = e.ColumnIndex

End Sub


'Write the below code in DataGridView SelectionChanged Event [ SAY DATAGRIDVIEW HAS 4 COLUMNS ]

Private Sub DataGridView1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGridView1.SelectionChanged

Try
Dim sl As Integer
sl = row

If DataGridView1.Rows.Count > 1 Then

Select Case col

Case 0, 1, 2

DataGridView1.CurrentCell = Me.DataGridView1(col + 1, row)

Case 3

DataGridView1.CurrentCell = Me.DataGridView1(0, row + 1)

End Select

End If


Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub


Click here for coverting VB .NET to C#.NET and viseversa

Auto Complete in Datagrid

VB.NET example


Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Dim scAutoComplete As New AutoCompleteStringCollection
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.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 [Order Details]", conn)
da.Fill(ds, "Orders")
DataGridView1.DataSource = ds.Tables("Orders")
For x As Integer = 1 To 61000
scAutoComplete.Add(x.ToString)
Next
End Sub
Private Sub DataGridView1_CellValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles DataGridView1.CellValidating
If e.ColumnIndex = 1 Then
Debug.Print(e.FormattedValue.ToString)
If Not scAutoComplete.Contains(e.FormattedValue.ToString) Then
MessageBox.Show("Invalid Entry")
e.Cancel = True
End If
End If
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# example

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 CSAutoComplete
{
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 [Order Details]", conn);
da.Fill(dt);
dataGridView1.DataSource = dt;
for (int x = 1; x <= 61000; x++ )
{
scAutoComplete.Add(x.ToString());
}
}
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;
}
}
private void dataGridView1_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
{
if(e.ColumnIndex==1)
{
if(!scAutoComplete.Contains(e.FormattedValue.ToString()))
{
MessageBox.Show("Invalid Data");
e.Cancel=true;
}
}
}
}
}

FILE HANDLIND IN .NET

CREATING A FILE

Dim oFile As New IO.FileInfo("serverName.txt")

oFile.Create()

DELETING A FILE

Dim oFile As New IO.FileInfo("serverName.txt")

OFILE.DELETE()

WRITING TO FILE

Method 1:

Dim fr As New System.IO.StreamWriter("path as string")

fr.Write("value as string")

fr.Close()

Method 2:

Dim oFile As New IO.FileInfo("serverName.txt")

Dim fs As IO.FileStream

fs = oFile.Create()

Dim info As Byte() = New System.Text.UTF8Encoding(True).GetBytes("This is some text in the file.")

' Add some information to the file.

fs.Write(info, 0, info.Length)

fs.Close()

READING FROM FILE

Method 1:

Dim fr As New System.IO.StreamReader( "serverName.txt")

Dim conStr As String = ""

conStr = fr.ReadLine()

Method 2:

            Dim sr As StreamReader = File.OpenText(path)
           Do While sr.Peek() >= 0
               Console.WriteLine(sr.ReadLine())
           Loop
           sr.Close()

Determine which radiobutton is checked in group of radiobuttons

Private Sub Options_CheckedChanged( _ByVal sender As System.Object, _ByVal e As System.EventArgs) _Handles rd1.CheckedChanged,rd2.CheckedChecked

Dim c As RadioButton = CType(sender, RadioButton)

select case c.Name

case "rd1"
'Write code here for first radio button
case "rd2"
'Write code here for second radio button

End select

End Sub

Getting all Sql Servers In a Network in .NET

//first add reference of Microsoft SQLDMO Object Library

SQLDMO.Application app = new SQLDMO.ApplicationClass();
SQLDMO.NameList names = app.ListAvailableSQLServers();
string serverName = "";

for (int i = 1; i <= names.Count; ++i)
{
serverName = names.Item(1);
MessageBox.Show(serverName);
}

CHANGING BACK COLOUR OF AN MDI PARENT

CHANGING BACK COLOUR OF AN MDI PARENT

IN VB.NET

Me.LayoutMdi(MdiLayout.Cascade)
Dim ctl As Control
Dim ctlMDI As MdiClient

For Each ctl In Me.Controls

Try
ctlMDI = CType(ctl, MdiClient)
ctlMDI.BackColor = Color.SteelBlue

Catch exc As InvalidCastException

End Try

Next

IN C#.NET

this.LayoutMdi(MdiLayout.Cascade);

Control ctl;
MdiClient ctlMDI;

foreach (Control ctl in this.Controls) 
                {                    
                    try
                    {
                        ctlMDI = (MdiClient)ctl;
                        ctlMDI.BackColor = Color.SteelBlue;
                    }

                    catch (InvalidCastException ex)
                    {
                       // MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }

Retriving Image from Sql Server in .NET

Retriving Image from Sql Server in .NET

Dim img1 As Byte()

Dim cmd As New OleDbCommand("select image from companyLogo", Conn)
Dim dr As OleDbDataReader

dr = cmd.ExecuteReader()
img1 = Nothing

While dr.Read

img1 = dr(0)

End While

Dim stream1 As New MemoryStream(img1)

Dim newImage As Image
newImage = Image.FromStream(stream1)
PictureBox1.Image = newImage
stream1.Close()


Click here for coverting VB .NET to C#.NET and viseversa

Saving Image to sql server in .NET

Saving Image to sql server in VB.NET

Dim oImg As FileStream
Dim oBinaryReader As BinaryReader
Dim oImgByteArray As Byte()

oImg = New FileStream(txtPath.Text, FileMode.Open, FileAccess.Read)
oBinaryReader = New BinaryReader(oImg)
oImgByteArray = oBinaryReader.ReadBytes(oImg.Length)

oBinaryReader.Close()
oImg.Close()

Pass this oImgByteArray to the query

NOTE: Sometimes query wont work then we have to use stored procedure for storing images and parameterized query for MS Access.

eg: in MS SQl
Try
Dim cmd As New SqlCommand()
cmd.CommandText = "AddImage"
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@image",oImgByteArray)

If con.State = ConnectionState.Closed Then
con.Open()
End If
cmd.Connection = con
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
con.Close()
End Try

eg: in MS Access

cmd.CommandText = insert into tablename values(@image) ;
cmd.Parameters.Add("@image",oImgByteArray );

OleDbParameter par1 = new OleDbParameter("@image", image1);
par1.SourceColumn = "image";
cmd.Parameters.Add(par1);

cmd.Connection = conn ;
cmd.ExecuteNonQuery();
------------------------------------------------------------------------------------------------------------------------



Click here for coverting VB .NET to C#.NET and viseversa

Panel Movement in .NET

Moving Panels at run time using VB .NET

'Declare p1move as public as
Dim p1move as Integer = 0

Private Sub Panel1_MouseDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles Panel1.MouseDown


If e.Button = "1048576" Then
p1move = 1
Else
p1move = 0
End If


End Sub


Private Sub Panel1_MouseMove(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles Panel1.MouseMove

If p1move = 1 Then
Me.Location = New System.Drawing.Point(Me.Location.X + (e.X - curX), Me.Location.Y + (e.Y - curY))
Else
curX = e.X
curY = e.Y
End If


End Sub


Private Sub Panel1_MouseUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles Panel1.MouseUp

p1move = 0

End Sub



Click here for coverting VB .NET to C#.NET and viseversa

ADDING CONTROLS AND EVENTS DYNAMICALLY

ADDING CONTROLS DYNAMICALLY

//I used Flow layout Panel for auto arrangement
/**** HERE WE ARE ADDING CONTROLS TO FLOW LAYOUT PANEL*****/

Button btn = new Button();

btn.Name = dt.Rows[i][0].ToString();
btn.Text = dt.Rows[i][0].ToString();

btn.ContextMenuStrip = contextMenuStrip1;

btn.Width = 150;
btn.Height = 75;

btn.Click+=new EventHandler(btn_Click);

flowLayoutPanel1.Controls.Add(btn);

//'HERE WE HAVE TO WRITE THE CODE FOR BUTTON CLICK

private void btn_Click(object sender, EventArgs e)
{
try
{
Button bt;
bt = (Button)sender;

//write code here
}
catch (Exception ex)
{
}
}