Omarsoft For IT Solutions (Java Codes ,C#.NET Codes , ASP.NET Codes ,VB.NET Codes ,Oracle Database Administration, Real Application Cluster , Remote Support, Cloud Services , Networks ,Virtualization....
  • الأنظــمــة المكتبية        Windows App Programming
  • أنظــمـةالويــب        Web based systems programming
  • تطبيقات الهواتف الذكية     smartphones programming
  • إدارة قواعــــــد البيــــــــــــــــانات        Database Administration
  • إدارة الشبكـــــــــــــــــــــــــــــــــات        Networks Administration
  • إدارة الســـيــرفرات (ويب - محلية)  Servers Administration
  • إدارة مخـــــــــــــــــازن البيــــــــــــانات     Storage Administration
  •             N Computing & 2X Application services

    Social Icons

Loading...

SQL Server Database Backup and Restore in C# .NET



Before coding, you must set the reference to the SMO assembly. You need to add these components:
  1. Microsoft.SqlServer.Smo
  2. Microsoft.SqlServer.SmoExtended
  3. Microsoft.SqlServer.ConnectionInfo
  4. Microsoft.SqlServer.Management.Sdk.Sfc
After Adding References, you need to add 2 using statements:
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

The Code

The following code creates connection with SQL Server. To execute:
"select * from sys.databases"
The above query retrieves all databases from SQL Server.
public void Createconnection()
{
DBbackup.DataBaseClass dbc = new DataBaseClass();
cbdatabasename.Items.Clear();
// select * from sys.databases getting all database name from sql server
cmd = new OdbcCommand("select * from sys.databases", dbc.openconn());
dr = cmd.ExecuteReader();
while (dr.Read())
{
cbdatabasename.Items.Add(dr[0].ToString());
}
dr.Close();
}
The following code gets server names that exist. To execute:
"select *  from sys.servers"
The above query retrieves servers:
public void serverName()
{
DBbackup.DataBaseClass dbc = new DataBaseClass();
// select * from sys.servers getting server names that exist
cmd = new OdbcCommand("select * from sys.servers", dbc.openconn());
dr = cmd.ExecuteReader();
while (dr.Read())
{
cbservername.Items.Add(dr[1].ToString());
}
dr.Close();
}

Database Backup

public void backup(string str)
{
if (string.IsNullOrEmpty(cbservername.Text) | string.IsNullOrEmpty(cbdatabasename.Text))
{
MessageBox.Show("Server Name & Database can not be Blank");
return;
}
else
{
if (str == "backup")
{
saveFileDialog1.Filter = "Text files (*.bak)|*.bak|All files (*.*)|*.*";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
// the below query get backup of database you specified in combobox
query("Backup database " + cbdatabasename.Text +
" to disk='" + saveFileDiaog1.FileName + "'");
MessageBox.Show("Database BackUp has been created successfully.");
}
}
}
}

Database Restore

public void Restore(OdbcConnection sqlcon, string DatabaseFullPath, string backUpPath)
{
using (sqlcon)
{
string UseMaster = "USE master";
OdbcCommand UseMasterCommand = new OdbcCommand(UseMaster, sqlcon);
UseMasterCommand.ExecuteNonQuery();
// The below query will rollback any transaction which is running on database
string Alter1 = @"ALTER DATABASE
[" + DatabaseFullPath + "] SET Single_User WITH Rollback Immediate";
OdbcCommand Alter1Cmd = new OdbcCommand(Alter1, sqlcon);
Alter1Cmd.ExecuteNonQuery();
// The below query will restore database file from disk where backup was //taken ....
string Restore = @"RESTORE DATABASE
[" + DatabaseFullPath + "] FROM DISK = N'" +
backUpPath + @"' WITH FILE = 1, NOUNLOAD, STATS = 10";
OdbcCommand RestoreCmd = new OdbcCommand(Restore, sqlcon);
RestoreCmd.ExecuteNonQuery();
// the below query change the database back to multiuser
string Alter2 = @"ALTER DATABASE
[" + DatabaseFullPath + "] SET Multi_User";
OdbcCommand Alter2Cmd = new OdbcCommand(Alter2, sqlcon);
Alter2Cmd.ExecuteNonQuery();
Cursor.Current = Cursors.Default;
}
}

Using of Code:

create new project (C#) :

  1. --- In Designer Mode:

Add new form --> add 2 Combobox  (cbservername , cbdatabasename).
Add 2 buttons (backupbtn , restorebtn) 

     2.--- In the Code Behind of form :

- in form_load event:
 private void Form1_Load(object sender, EventArgs e)
        {
                       
            serverName();
        }

- in cbservername_SelectedIndexChanged event write:
private void cbservername_SelectedIndexChanged(object sender, EventArgs e)
        {
            Createconnection();
        }
       
- in backupbtn_click event:
 private void backupbtn_Click(object sender, EventArgs e)
        {
            backup("backup");
        }
- in restorebtn_click event:
private void restorebtn_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(cbservername.Text) | string.IsNullOrEmpty(cbdatabasename.Text))
            {
                MessageBox.Show("Server Name & Database can not be Blank", "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {

                openFileDialog1.Filter = "Text files (*.bak)|*.bak|All files (*.*)|*.*";

                DBbackup.DataBaseClass dbc = new DataBaseClass();
                Restore(dbc.openconn(), cbdatabasename.SelectedItem.ToString(), openFileDialog1.FileName);
               MessageBox.Show("Database Backup file has been restore successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
        }

Difference between LINQ to SQL and Entity Framework


LINQ to SQL allow you to query and modify SQL Server database by using LINQ syntax. Entity framework is a great ORM shipped by Microsoft which allow you to query and modify RDBMS like SQL Server, Oracle, DB2 and MySQL etc. by using LINQ syntax. Today, EF is widely used by each and every .NET application to query to database. The difference between LINQ to SQL and EF is given below.

LINQ to SQL
Entity Framework
It only works with SQL Server Database.
It can works with various databases like Oracle, DB2, MYSQL, SQL Server etc.
It generates a .dbml to maintain the relation
It generates an .edmx files initially. The relation is maintained using 3 different files .csdl, .msl and .ssdl
It has not support for complex type.
It has support for complex type.
It cannot generate database from model.
It can generate database from model.
It allows only one to one mapping between the entity classes and the relational tables /views.
It allows one-to-one, one-to-many & many-to-many mappings between the Entity classes and the relational tables /views
It allows you to query data using DataContext.
It allows you to query data using EntitySQL, ObjectContext, DbContext.
It provides a tightly coupled approach.
It provides a loosely coupled approach. Since its code first approach allow you to use Dependency Injection pattern which make it loosely coupled .
It can be used for rapid application development only with SQL Server.
It can be used for rapid application development with RDBMS like SQL Server, Oracle, DB2 and MySQL etc.

An Introduction to Entity Framework for Beginners


Introduction

This article introduces Entity Framework to absolute beginners. The article is meant for developers who are primarily using ADO.NET to write their data access layers. Many experienced developers will find this article very basic but since the article is written from the perspective of beginners, I've tried to keep things simple.

Background

ADO.NET is a very strong framework for data access. ADO.NET has been around since many years and there are a lot of systems running over ADO.NET. Developers who are totally oblivious to the concept of ORMs will probably be asking "What is Entity Framework? What are the benefits of using it and is it an alternative to ADO.NET?"
Well, to answer the first question about what is Entity Framework, Entity Framework is an Object Relational Mapper (ORM). It basically generates business objects and entities according to the database tables and provides the mechanism for:
  1. Performing basic CRUD (Create, Read, Update, Delete) operations.
  1. Easily managing "1 to 1", "1 to many", and "many to many" relationships.
  1. Ability to have inheritance relationships between entities.
and to answer the second question, the benefits are:
  1. We can have all data access logic written in higher level languages.
  1. The conceptual model can be represented in a better way by using relationships among entities.
  1. The underlying data store can be replaced without much overhead since all data access logic is present at a higher level.
and finally, the last question that whether it is an alternative to ADO.NET, the answer would be "yes and no". Yes because the developer will not be writing ADO.NET methods and classes for performing data operations and no because this model is actually written on top of ADO.NET, meaning under this framework, we are still using ADO.NET. So let us look at the architecture of Entity Framework (diagram taken from MSDN):
Entity framework article image

Using the code

Let's try to understand the ease of use that Entity Framework provides by performing simple CRUD operations. Once we look at the code and how effortlessly and efficiently we can do these operations, the benefits of Entity Framework will become quite obvious.
Creating the database
Let's have a simple database with one table. Let's create a simple table for Contacts and we will perform CRUD operations on this table.
Entity framework article image
Adding the Entity Model to the Website
Once we have the database ready, we can add the entity model to our website. We can do this by adding anADO.NET Entity Data Model to the website.
Entity framework article image
Once we select to add this data model to our website, we will have to select the approach we want to take for our Model's contents.
Entity framework article image
What this selection means is that we can either choose to generate the entity model from an existing database schema or we can design the entity model here and then later hook it up to the database. Since we already have the database ready, we will use the first option. Once the Model is generated, the Entity for each table is generated. The generated entity for our contact table is:
Entity framework article image
Also, the classes for performing database operations are also created. We just need to know how to use these classes to perform database operations.
Entity framework article image
Insert operation
Let us create a simple page to perform an insert operation.
Entity framework article image
Now once the user chooses to insert the values into the database, the actual data operation can be performed by using the AddObject method of the Model class entity collection. The following code snippet show how to perform the insert.

Contact con = new Contact();
con.fname = TextBox1.Text;
con.lname = TextBox2.Text;
con.phone = TextBox3.Text;
ContactsDb db = new ContactsDb();
db.Contacts.AddObject(con);
db.SaveChanges();
This will insert the record into the table. You can notice the simplicity and efficiency of the code we wrote to perform the insertion.
Reading all the records
There are scenarios when we want to read all records. Let's say we are making a page that will display all the contact information in a single page.
Entity framework article image
We can retrieve the collection of Entities using the Model object to achieve this. The code snippet below will show how that can be done.

ContactsDb db = new ContactsDb();
Repeater1.DataSource = db.Contacts;
Repeater1.DataBind();
Selecting a specific record
If we want to select a specific record from the table, we can use the SingleOrDefault method on the Model's entities collection. Let's say we want the functionality of updating/deleting a record on a single page then we will first have to select the record based on the ID, then update/delete the selected record.
Entity framework article image
Selection of any particular record (Contact) based on ID can be done as:

int idToupdate = Convert.ToInt32(Request.QueryString["id"].ToString());
ContactsDb db = new ContactsDb();
Contact con = db.Contacts.SingleOrDefault(p => p.id == idToupdate);
Once this code is executed, the Contact object will contain the required values.
Updating the record
If we want to update a record, then a simple update operation can be performed as:

int idToupdate = Convert.ToInt32(Request.QueryString["id"].ToString());
ContactsDb db = new ContactsDb();
Contact con = db.Contacts.SingleOrDefault(p => p.id == idToupdate); con.phone = TextBox1.Text;
db.SaveChanges();
Once this code executes, the value of phone number will be updated by a new value which is retrieved fromTextBox1.
Deleting a record
If we want to delete a particular record then we can perform a delete operation by using the DeleteObjectfunction. The following code snippet demonstrates the same:

//delete this contact
int idToupdate = Convert.ToInt32(Request.QueryString["id"].ToString());
ContactsDb db = new ContactsDb();
Contact con = db.Contacts.SingleOrDefault(p => p.id == idToupdate); 

db.Contacts.DeleteObject(con);
db.SaveChanges();


Now that we have the basic CRUD operations performed on the database using the Entity Framework.
A note on Relationships and Lazy Loading
To understand the Entity Framework we need to understand some things like naming conventions, relationships between tables, and relationships between entities. Also, the concept of lazy loading once fully understood will give the power to the developer to use the Entity Framework efficiently.
Note: Since this is an introductory article on Entity Framework we have not discussed these things. Perhaps we will discuss them in a separate article.
Entity Framework has been in use for some time now. But there are many developers who are still getting started with Entity Framework. This article was meant as an overview of the Entity Framework. This should not be treated as a complete tutorial. Also, the code written is very simple and there is a lot of scope for optimization but since the idea here is to understand Entity Framework, I tried to keep the code simple and readable rather and optimal.

Sana'a Yemen - 50th st.

+967 738166685

omar.soft2000@gmail.com

للاتصال بنا CONTACT US

الاسم Name

بريد إلكتروني Email *

رسالة Message *

2015-2023 © All Rights Reserved Omarsoft
Back To Top