Các phương thưc phổ biến của lớp kết nối CSDL( DataAccessDA)

Trong phần này tôi trình bày các phương thức phổ biến hay đươc dùng của DataAccessDA. Các phương thức này bao gồm truy vấn trả về dữ liệu và thưc thi dữ liệu.

lass DataAccessDA
{
private static string conn = “”;
SqlConnection connect = new SqlConnection();

public SqlConnection OpenConnect()
{
if (connect.State == ConnectionState.Closed || connect == null)
{
if (conn == “”)
GetConnectString();
connect = new SqlConnection(conn);
connect.Open();
}
return connect;
}

public void CloseConnect()
{
if (connect.State == ConnectionState.Open)
connect.Close();
if (connect.State == ConnectionState.Connecting)
connect.Close();
if (connect.State == ConnectionState.Executing)
connect.Close();
if (connect.State == ConnectionState.Fetching)
connect.Close();
if (connect.State == ConnectionState.Broken)
connect.Close();

connect.Dispose();
}

private void GetConnectString()
{
Configuration config =
ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
ConnectionStringsSection csSection = config.ConnectionStrings;
conn = csSection.ConnectionStrings[2].ToString();
}

public bool TestConnection()
{
try
{
OpenConnect();
if (connect.State == ConnectionState.Open)
return true;
else
return false;
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public SqlDataReader GetDataReader(string query)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = OpenConnect();
SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch (Exception ex) { throw new Exception(ex.Message); }
}

public SqlDataReader GetDataReader(string query, SqlParameter[] param)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
cmd.Connection = OpenConnect();
SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch (Exception ex) { throw new Exception(ex.Message); }
}

public DataTable GetDataTable(string query)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = OpenConnect();
SqlDataAdapter dapter = new SqlDataAdapter(cmd);
DataTable result = new DataTable();
dapter.Fill(result);
return result;
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public DataTable GetDataTable(string query, SqlParameter[] param)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
cmd.Connection = OpenConnect();
SqlDataAdapter dapter = new SqlDataAdapter(cmd);
DataTable result = new DataTable();
dapter.Fill(result);
return result;
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public DataSet GetDataSet(string query)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = OpenConnect();
SqlDataAdapter dapter = new SqlDataAdapter(cmd);
DataSet result = new DataSet();
dapter.Fill(result);
return result;
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public DataSet GetDataSet(string query, SqlParameter[] param)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
cmd.Connection = OpenConnect();
SqlDataAdapter dapter = new SqlDataAdapter(cmd);
DataSet result = new DataSet();
dapter.Fill(result);
return result;
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public object GetExecuteScalar(string query)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = OpenConnect();
return cmd.ExecuteScalar();
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public object GetExecuteScalar(string query, SqlParameter[] param)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
cmd.Connection = OpenConnect();
return cmd.ExecuteScalar();
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public int ExecuteCommand(string query)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = OpenConnect();
return cmd.ExecuteNonQuery();
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public int ExecuteCommand(string query, SqlParameter[] param)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
cmd.Connection = OpenConnect();
return cmd.ExecuteNonQuery();
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public int ExecuteCommand(string query, SqlConnection connect,
SqlTransaction transaction)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = transaction;
cmd.Connection = connect;
return cmd.ExecuteNonQuery();
}
catch (Exception ex) { throw new Exception(ex.Message); }
}

public int ExecuteCommand(string query, SqlConnection connect,
SqlTransaction transaction, SqlParameter[] param)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = transaction;
cmd.Parameters.AddRange(param);
cmd.Connection = connect;
return cmd.ExecuteNonQuery();
}
catch (Exception ex) { throw new Exception(ex.Message); }
}

public object ExecuteCommandWithOutput(string query,
SqlParameter[] param, string output)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
cmd.Parameters[output].Direction = ParameterDirection.Output;
cmd.Connection = OpenConnect();
cmd.ExecuteNonQuery();
return cmd.Parameters[output].Value;
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public object ExecuteCommandWithOutput(string query, SqlParameter[] param,
SqlConnection connect, SqlTransaction transaction, string output)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = transaction;
cmd.Parameters.AddRange(param);
cmd.Parameters[output].Direction = ParameterDirection.Output;
cmd.Connection = connect;
cmd.ExecuteNonQuery();
return cmd.Parameters[output].Value;
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public object ExecuteCommandWithReturnvalue(string query,
SqlParameter[] param, string returnValue)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
cmd.Parameters[returnValue].Direction = ParameterDirection.ReturnValue;
cmd.Connection = OpenConnect();
cmd.ExecuteNonQuery();
return cmd.Parameters[returnValue].Value;
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public object ExecuteCommandWithReturnvalue(string query,
SqlParameter[] param, SqlConnection connect, SqlTransaction transaction, string returnValue)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
cmd.Parameters[returnValue].Direction = ParameterDirection.ReturnValue;
cmd.Transaction = transaction;
cmd.Connection = connect;
cmd.ExecuteNonQuery();
return cmd.Parameters[returnValue].Value;
}
catch (Exception ex) { throw new Exception(ex.Message); }
finally
{
CloseConnect();
}
}

public DataTable GetDataTableFromXMLFile(string path)
{
DataTable result = new DataTable();
result.ReadXml(path);
if (result == null)
result = new DataTable();
return result;
}
}

Về tieuyeuit
Lập trình viên Asp.net Tôi chia sẻ một,bạn chia sẻ một và chúng ta có rất nhiều!

2 Responses to Các phương thưc phổ biến của lớp kết nối CSDL( DataAccessDA)

  1. Sylvitan nói:

    Hello ! I’m new on this forum, hope to talk to you soon🙂
    I love carsn seotons and tuning, and you ?

  2. Pingback: eurasian culture

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

%d bloggers like this: