using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; namespace Yaadein.Data { internal class DBHelper { //private string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString(); public static SqlConnection GetConnecton() { return new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ToString()); } /// <summary> /// This will returen the data with insertion / updation / deletion /// </summary> /// <param name="strQuery">Pass your query accordingly</param> /// <returns>This will return DataSet with multiple or single table</returns> public DataSet SetDataWithReturn(string strQuery) { SqlConnection con = GetConnecton(); SqlDataAdapter da = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(); DataSet ds = new DataSet(); try { cmd.CommandType = CommandType.Text; cmd.Connection = con; cmd.CommandText = strQuery; con.Open(); da.SelectCommand = cmd; da.Fill(ds); } catch (Exception ex) { throw new Exception("Some Server Error Please Try Again!"); } finally { con.Close(); da.Dispose(); cmd.Dispose(); con.Dispose(); } return ds; } public long SetData(string strQueryOrProcedure, CommandType cmdType, List<dbparameter> ParamertWithValue,string outputID) { SqlCommand cmd = new SqlCommand(); SqlConnection con = DBHelper.GetConnecton(); cmd.Connection = con; cmd.CommandType = cmdType; cmd.CommandText = strQueryOrProcedure; try { con.Open(); for (int i = 0; i < ParamertWithValue.Count<dbparameter>(); i++) { cmd.Parameters.AddWithValue(ParamertWithValue[i].Name, ParamertWithValue[i].Values); } SqlParameter sqlPara = new SqlParameter(outputID, SqlDbType.BigInt); sqlPara.Direction = ParameterDirection.Output; cmd.Parameters.Add(sqlPara); cmd.ExecuteNonQuery(); return long.Parse(cmd.Parameters[outputID].Value.ToString()); } catch { throw; } finally { con.Close(); cmd.Dispose(); con.Close(); } } public bool SetData(string strQueryOrProcedure, CommandType cmdType, List<dbparameter> ParamertWithValue) { SqlCommand cmd = new SqlCommand(); SqlConnection con = DBHelper.GetConnecton(); cmd.Connection = con; cmd.CommandType = cmdType; cmd.CommandText = strQueryOrProcedure; try { con.Open(); for (int i = 0; i < ParamertWithValue.Count<dbparameter>(); i++) { cmd.Parameters.AddWithValue(ParamertWithValue[i].Name, ParamertWithValue[i].Values); } cmd.ExecuteNonQuery(); return true; } catch { throw; } finally { con.Close(); cmd.Dispose(); con.Close(); } } public object GetIdentiryOrID(string strQueryOrProcedure, CommandType cmdType, List<dbparameter> ParamertWithValue) { SqlCommand cmd = new SqlCommand(); SqlConnection con = DBHelper.GetConnecton(); cmd.Connection = con; cmd.CommandType = cmdType; cmd.CommandText = strQueryOrProcedure; try { con.Open(); for (int i = 0; i < ParamertWithValue.Count<dbparameter>(); i++) { cmd.Parameters.AddWithValue(ParamertWithValue[i].Name, ParamertWithValue[i].Values); } return cmd.ExecuteScalar(); } catch { throw; } finally { con.Close(); cmd.Dispose(); con.Close(); } } public DataSet GetData(string strQueryOrProcedure, CommandType cmdType, List<dbparameter> ParamertWithValue) { SqlCommand cmd = new SqlCommand(); SqlConnection con = DBHelper.GetConnecton(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); cmd.Connection = con; cmd.CommandType = cmdType; cmd.CommandText = strQueryOrProcedure; da.SelectCommand = cmd; try { con.Open(); for (int i = 0; i < ParamertWithValue.Count<dbparameter>(); i++) { cmd.Parameters.AddWithValue(ParamertWithValue[i].Name, ParamertWithValue[i].Values); } da.Fill(ds); return ds; } catch { throw; } finally { con.Close(); cmd.Dispose(); con.Close(); } } /// <summary> /// This show only the effected rows of the datatable with insertion / updation / deletion /// </summary> /// <param name="strQuery">Pass your query accordingly</param> /// <returns>This will return the number of affected rows</returns> public bool SetDataWithoutReturn(string strQuery) { SqlConnection con = GetConnecton(); SqlCommand cmd = new SqlCommand(); bool flag = true; try { cmd.CommandType = CommandType.Text; cmd.Connection = con; cmd.CommandText = strQuery; con.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { flag = false; throw new Exception("Some Server Error Please Try Again!"); } finally { con.Close(); cmd.Dispose(); con.Dispose(); } return flag; } /// <summary> /// This will return the dataSet accoding to your query /// </summary> /// <param name="strQuery">Pass your query accordingly</param> /// <returns>This will return DataSet with multiple or single table</returns> public DataSet GetData(string strQuery) { SqlConnection con = GetConnecton(); SqlDataAdapter da = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(); DataSet ds = new DataSet(); try { cmd.CommandType = CommandType.Text; cmd.Connection = con; cmd.CommandText = strQuery; con.Open(); da.SelectCommand = cmd; da.Fill(ds); } catch (Exception ex) { throw new Exception("Some Server Error Please Try Again!"); } finally { con.Close(); da.Dispose(); cmd.Dispose(); con.Dispose(); } return ds; } public long SetDataAndGetIdentity(string strQuery) { SqlConnection con = GetConnecton(); SqlCommand cmd = new SqlCommand(); long obj = new long(); try { cmd.CommandType = CommandType.Text; cmd.Connection = con; cmd.CommandText = strQuery; cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); cmd.CommandText = "Select @@Identity"; obj = long.Parse(cmd.ExecuteScalar().ToString()); } catch (Exception ex) { throw new Exception("Some Server Error Please Try Again!"); } finally { con.Close(); cmd.Dispose(); con.Dispose(); } return obj; } } }</dbparameter></dbparameter></dbparameter></dbparameter></dbparameter></dbparameter></dbparameter></dbparameter>
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)