DBConnect

• Download example (ZIP) - 174.9 KB
• Download example (RAR) - 160.4 KB

You can select a query in just three lines of code.

Introduction

It is very necessary to use database in applications for storing and reading data whether you are a beginner or a professional programmer. This class is based on DataTable object to read data from MS SQL database. For running a query, you need just to call a function to do it.

DBConnect can be used in C# applications and ASP.NET websites.

Total Code

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace EasyTeb
{
    public class DBConnect
    {
        SqlConnection connection;
        SqlCommand cmd;
        SqlDataAdapter adapter;
        bool isset = false;

        public DBConnect()
        {
            connection = new SqlConnection("Server=.\SQLEXPRESS;
                         Database=TalashNet;Integrated Security=True;");
        }

        public string Script(string Query)
        {
            if (isset)
            {
                try
                {
                    cmd = new SqlCommand(CheckInject(Query), connection);
                    object result = cmd.ExecuteScalar();
                    if (result == null)
                        return "1";
                    else
                        return result.ToString();
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }
            return "0";
        }

        public DataTable Select(string Query)
        {
            if (isset)
            {
                DataTable dt = new DataTable();
                adapter = new SqlDataAdapter(CheckInject(Query), connection);
                adapter.Fill(dt);
                return dt;
            }
            return new DataTable();
        }

        public void Connect()
        {
            if (!isset)
            {
                connection.Open();
                isset = true;
            }
        }

        public void DisConnect()
        {
            if (isset)
            {
                connection.Close();
                //connection = null;
                adapter = null;
                cmd = null;
                isset = false;
            }
        }

        public string CheckInject(string sql)
        {
            sql = sql.Replace("--", " ");
            sql = sql.Replace("/*", " ");
            //sql = sql.Replace('%', ' ');
            //sql.Replace('*', ' ');
            return sql;
        }

        public string CheckInjectText(string sql)
        {
            sql = sql.Replace(',', ' ');
            sql.Replace('$', ' ');
            sql.Replace('^', ' ');
            sql.Replace('%', ' ');
            return sql;
        }
    }
}

Using the Code

First, create a class file (.cs) and write the above code in it.

Then, go to your form for calling a query from SQL Server.

If you want to SELECT a query from SQL, you must use this code:

string query = "SELECT * FROM [MyTable]";
DBConnect db = new DBConnect();
db.Connect();
DataTable dt = db.Select(query);
db.DisConnect();

Now, we have a DataTable containing the results of your query. You can show it on a DataGridView or use its data in behind code.

Example of show DataTable in a GridView in ASP.NET:

GridView1.DataSource = dt;
GridView1.DataBind();

Example of show DataTable in a DataGridView in C# Windows application:

DataGridView1.DataSource = dt;

You must use this code if you want to DELETE or INSERT or UPDATE query to SQL:

DBConnect db = new DBConnect();
db.Connect();
db.Script(query);
db.DisConnect();

Points of Interest

This class helps programmer to easily use SQL Server. I hope this class will help you to write your programs faster than before.

History

I wrote this simple class in 2008. It is the second version of DBConnect.