Records Management

Natural disasters, litigation, and computer failure are a few of the reasons to have records storage procedures done professionally to prevent mistakes. A records management service organizes, classifies, archives, preserves, and purges physical files and electronic information. A records management service stores documents and data safely, allows for their access, provides specialized storage, and can destroy them properly when they are no longer needed.

If a company stores and manages their records in house, it is expensive and not secure for information, and mistakes can be made. Paper records, including highly confidential documents, need to be managed, organized, and stored securely off site. Some businesses choose a self storage facility for off site storage. This is not a secure place for company history! A records management service uses alarm systems to safely keep records in a facility that also protects from fire, rodents, and other risks.

Keep Records Accessible

Information sometimes needs to be retrieved by companies immediately, and it needs to be accessible. Not only does a records management service get the physical files off site to avoid taking up valuable space, but their system allows for access to specific information when needed. Having access to needed information helps protect a company legally, helps meet compliance with regulatory obligations, and increases efficiency.

Data Backup

The amount of data that needs to be digitally stored is overwhelming for a business. This data needs to be backed up and stored consistently while also allowing for easy access. A records management service can facilitate the data back up and safely store it, and allow for easy recovery.

A records management service offers off site and highly secure storage of media. Sometimes specialized storage of items is needed for things such as tapes, discs and CD ROMs, and a records management company has the facilities to store them appropriately. The information is not only safeguarded, but it is easily recovered.

Privacy, data protection, and identity theft are important current issues for records management, so paper shredding and document destruction are crucial for protecting your company from making mistakes. Not only criminals could be looking through trash or unsecured storage for confidential information, but competitors and the news media. The information should be destroyed thoroughly so confidentiality is not compromised.

Just open SQL-Server and start typing the following(for spoon feeding u can also Copy and Paste):

What are Joins- Joins retrieve combination of two tables on the basis of a desired match.

Types of Joins-

• Inner(Equi Join, Natural Join, Cross Join)
  


• Outer(Left Outer Join, Right Outer Join, Full Outer Join)
  


• Self Join
  

/* Note: #Join implicitly means Inner Join

Create First Table Department-

(

DepartmentID int PRIMARY KEY,

DepartmentName varchar(50)

/* Here DepartmentID will be Primary Key */

Insert some values in Department Table-

insert into Department values(1,'HR')

insert into Department values(3,'Establishment')

insert into Department values(4,'SoftwareDevelopment')

insert into Department values(5,'Clerical')

Create Second table Employee-

create table Employee

EmployeeID int PRIMARY KEY IDENTITY,

EmployeeName varchar(50),

DepartmentID int foreign key references Department(DepartmentID)

/* Here DepartmentID is foreign key */

Insert some values in EmployeeTable-

insert into Employee values('Jazz',2)

insert into Employee values('Mic',2)

insert into Employee values('Joe',3)

insert into Employee values('Sam',5)

insert into Employee values('Aby',5)

insert into Employee values('Jazz',3)

insert into Employee values('Rai',2)

insert into Employee values('Tarry',2)

insert into Employee values('Shally',1)

insert into Employee values('Akash',2)

select * from Department,Employee

where Department.DepartmentID=Employee.DepartmentID

/*The above query generates a a table containing all the records where DepartmentID in both the tables are same The same result can be obtained by performing inner join on both the tables*/

/*****INNER JOIN*****/

/*****Equi Join*****/

select * from Department INNER JOIN Employee

ON Department.DepartmentID=Employee.DepartmentID

/*The above query also fetches the same result as Equijoin(or Theta Join) is necessarily similar to Inner Join operation but the difference lies in the fact that Inner Join may be done on the basis of any other predicate condition other than '='

See tne following Example for this*/

select * from Department INNER JOIN Employee

ON Department.DepartmentID>Employee.DepartmentID

/*****Natural Join*****/

select * from Department NATURAL JOIN Employee

/*The above query generates same result with the only difference that Natural join gives only one identical column*/

Visit the link: http://blog.sqlauthority.com/...7/sql-server-get-common-records-from-two-tables-without-using-join*/

/*****Cross Join*****/

/*****OUTER JOIN*****/

/*****Left Outer Join*****/

/*****Right Outer Join*****/

ON Department.DepartmentID=Employee.DepartmentID

/*****SELF JOIN*****/

select A.EmployeeID,A.EmployeeName,B.EmployeeID,B.EmployeeName,A.DepartmentID

where A.DepartmentID=B.DepartmentID

That's all with joins!!

Figure 1.0: Screenshot of MySQL Utility.

Introduction

This article covers how to talk to MySQL database and extract the schema from it.

Background

As many of you know, MySQL is an open source database. It is free for non-commercial purposes. This article would be helpful for anybody doing development in MySQL and C#. What is the motivation behind this utility? Well, an application could crash in the middle of database activities. Often, these databases may not reside on the same site as your development/testing machine. We need a utility that would help us get a snapshot of the database at client site. If you have an utility that would capture the state of the client database, then, you can load it in your testing machine. The idea is to reproduce the situation your client faced. That way you can address any undiscovered issues.

Functionality Supported

• Saving Schema as Text


• Viewing Schema


• Viewing Entire Database

Required Tools

• ODBC.NET Data Provider from Microsoft


• MySQL Database


• MySQL ODBC Connector

Using the code

First, add a reference to Microsoft ODBC. Then use the using microsoft.odbc statement to tell it that you want to you MS ODBC. In short, OdbcConnection will be used to open connection, OdbcCommand to execute queries, and OdbcDataReader to read the resulting row set. The code shown below documents each step. You will notice, it runs the MySQL specific command SHOW TABLES to get the list of tables. Then it runs another query based on that particular table, SHOW COLUMNS IN CURRENT_TABLE. This is all the code does.

The Code

Collapse /*/////////////////////////////////////////////////////////////////////////
//
@@ Function:
@f ::PrepareSchema
//
@@ Description:
@d when this is called the widget is updated and everything
// about this database and tables are displayed
//
@@ Type:
@t public
//
@@ Arguments:
// none.
@@ Returns:
@r void
//
@@ Preconditions:
@c Provided that the GUI is running and DB Connection is made.
//
@@ Postconditions:
@o DB schema displayed
//
@@ References:
@e http://www.pcquest.com/content/linux/handson/102031401.asp%22\" target="_blank">Query MySql with C#.
//
/////////////////////////////////////////////////////////////////////////*/

public void PrepareSchema()
{
// create the connection object by setting the DSN

OdbcConnection ocConnection = new OdbcConnection("DSN="+ strDSN);

// second connection is created so we could make

// queries while executing one

OdbcConnection ocConnection2 = new OdbcConnection("DSN="+ strDSN);

// this will open up both connections

ocConnection.Open();
ocConnection2.Open();

// declare the commands for each table and column

OdbcCommand ocTableCommand;
OdbcCommand ocColumnCommand;

// create a command object. this will execute SHOW TABLES

// query. In mysql, it shows all of the tables contained in

// the database in use.

ocTableCommand = new OdbcCommand("SHOW TABLES", ocConnection);

// declare reader objects for tables and columns

OdbcDataReader odrTableReader;
OdbcDataReader odrColumnReader;

// queries that return result set are executed by ExecuteReader()

// If you are to run queries like insert, update, delete then

// you would invoke them by using ExecuteNonQuery()

odrTableReader = ocTableCommand.ExecuteReader();

// place create db statement in rich text box

rchtxtSchema.Text += "CREATE DATABASE ";
rchtxtSchema.Text += ocConnection.Database;
rchtxtSchema.Text += ";\r\n\r\n";

rchtxtSchema.Text += "USE DATABASE ";
rchtxtSchema.Text += ocConnection.Database;
rchtxtSchema.Text += ";\r\n\r\n";

string strTable = "";
string strColumnName = "";
string strColumnType = "";
string strColumnNull = "";
string strColumnPKey = "";
string strColumnDflt = "";
string strColumnExtr = "";

// reader the set of tables

while(odrTableReader.Read())
{
// here we are expecting rows with only 1 column

// containing the table name. that's why explcity

// call GetString() at 0th index

strTable = odrTableReader.GetString(0);

rchtxtSchema.Text += "CREATE TABLE ";
rchtxtSchema.Text += strTable;
rchtxtSchema.Text += "\r\n(\r\n";

// build up the command for each table

ocColumnCommand = new OdbcCommand("SHOW COLUMNS IN " +
strTable, ocConnection2);

// run the query

odrColumnReader = ocColumnCommand.ExecuteReader();

// reading the set of columsn

while(odrColumnReader.Read())
{
// This query returns the name of column, Type,

// wherther it's Null, whether it's primary Key,

// the default value, and extra info such as

// whether it's autoincrement or not

strColumnName = odrColumnReader.GetString(0);
strColumnType = odrColumnReader.GetString(1);
strColumnNull = odrColumnReader.GetString(2);
strColumnPKey = odrColumnReader.GetString(3);
//strColumnDflt = odrColumnReader.GetString(4);

strColumnExtr = odrColumnReader.GetString(5);

if (!strColumnNull.Equals("YES"))
strColumnNull = " NOT NULL ";
else
strColumnNull = "";

if (strColumnPKey.Equals("PRI"))
strColumnPKey = " PRIMARY KEY ";

//this.rchtxtSchema.Text += "\n";

rchtxtSchema.Text += " ";
rchtxtSchema.Text += strColumnName;
rchtxtSchema.Text += " ";
rchtxtSchema.Text += strColumnType;
rchtxtSchema.Text += strColumnPKey;
rchtxtSchema.Text += strColumnNull;
rchtxtSchema.Text += ",";
rchtxtSchema.Text += "\r\n";

}

rchtxtSchema.Text = this.rchtxtSchema.Text.Substring(0,
this.rchtxtSchema.Text.Length-3);
rchtxtSchema.Text += "\r\n);\r\n\r\n";

// free up the reader object

odrColumnReader.Close();
}

// close the reader

odrTableReader.Close();

// disconnect

ocConnection.Close();
ocConnection2.Close();
}

Points of Interest

Initially, I kept going back and forth from ODBC, ADODB and OLEDB to implement this. According to MySQL, it is not safe to use OLEDB. There was no mention of how to utilize OLEDB to perform simple database tasks. At the end, it was decided doing this would be very simple in ODBC. You have probably noticed that I used built-in commands (i.e. show tables) that the specific DB provider uses. I am definitely open to any suggestions or working examples of standards that work with MySQL.

HOW-TO use this Demo

Saving Schema

First, click on Select the Target Database. This should produce the dialog box showing the list of system as well as user DSNs.

Figure 2.0 - Depicts the DSN dialog form.

Now, click on Save Schema File As and select where you wish to save the file.

Figure 3.0 - Depicts the File Save dialog.

Next, make sure Save Schema is checked. Then click on Run.

Figure 4.0 - Depicts the status of operation. In this example, the program successfully wrote C:\s01user38_schema.txt.

Viewing Schema

First, uncheck Save Schema and then check View Schema. It should produce the output as depicted in figure 5.0

Figure 5.0 - Depicts the schema of s01user38.

Viewing Database

Now, click on View Database in Grid and press Run.

Figure 6.0 - Depicts the result of the query. It will show + initially. You have to click on it to expand all. Then it will show the tables as shown in this graphic. Then click on each blue link to see the rowset they contain.