RSS

c# database access class

22 Jun
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace IDStore.db
{
class DBAccess
{
SqlConnection conn;
string table = "tbl_Accounts";
AutoCompleteStringCollection autoCompleteCollection = new AutoCompleteStringCollection();

public DBAccess()
{
conn = ConnectionManager.GetConnection();
}

public void Openconn()
{
if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
{
conn.Open();
}

}

public void Closeconn()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}

}

public int ExecuteNonQuery(string query)
{
int result = 0;
conn.Open();

try
{
SqlCommand cmd = new SqlCommand();
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
result = cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return result;
}

public DataSet ExecuteQuery(string query)
{
conn.Open();
try
{
SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
DataSet ds = new DataSet();
adapter.Fill(ds, "Table");
return ds;

}
finally
{
conn.Close();
}
}

public List<Account> getAllAccountsASList()
{
List<Account> list = new List<Account>();
conn.Open();

SqlCommand cmd = new SqlCommand();
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM " + table;
SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
int accountID = dr.GetInt32(0);
string type = dr.GetString(1);
string username = dr.GetString(2);
string password = dr.GetString(3);
string description = dr.GetString(4);
list.Add(new Account { AccountID = accountID, Type = type, Username = username, Password = password, Description = description });
}
return list;
}

public bool HasRows(string query)
{
conn.Open();
try
{
SqlDataReader dr;
SqlCommand cmd = new SqlCommand();
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
dr = cmd.ExecuteReader();

if (dr.HasRows)
{
return true;
}

}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}

return false;
}
}
}

Download the complete sample project

Advertisements
 
Leave a comment

Posted by on June 22, 2012 in C#.Net

 

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: