create generic sql reader

What I am trying to do is create generic execute functions that I can call  and pass the query so I dont have to constantly write each over and over
for example:
  public void ExecuteSql(string sqlStr)
        {
            using (SqlConnection con = new SqlConnection(globalconnStr))
            using (SqlCommand cmd = new SqlCommand(sqlStr, con)) 
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlStr;
                cmd.Connection.Open();
                cmd.ExecuteScalar();
            }
        }

Open in new window

create a method add the query and call the needed method like ExecuteSql(sqlStr);
Is there any way to do that for a generic reader, dataset etc.
LVL 6
r3nderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
What do you mean by "generic"? You mean in the sense that it isn't tied to a specific provider, or something else?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think what you are looking for is STATIC functions, each with the return type you need
apart from that, not sure what exactly you are looking for?
0
r3nderAuthor Commented:
OK if I call a method from my code. say it is called getJobList(1,"render");
in that getjoblist  we have
public List<jobList>getJobList(int mode,string name)
{
sqlStr = string.Empty;
sqlStr += "Select company,location,uploaddate WHERE mode= "+mode+" and name = '"+ name +"'";
List<jobList> newJobList = new List<jobList>();
instead of writing out a reader for this data I want a generic reader I can call to process the data for me
**For example only**
if I wanted a dataset I would
Dataset ds = new Dataset();
try
{
ds = ExecuteDataSet(sqlStr);
}
catch(Exceptiion ex)
{
    MessageBox.Show(ex.message);
}

}
In this instance I would want a reader but I used a dataset to convey the message as shown by the code below

 public void ExecuteSql(string sqlStr)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(globalconnStr))
                using (SqlCommand cmd = new SqlCommand(sqlStr, con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlStr;
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch
            {
                //eat exception
            }
        }
        public void ExecuteScalar(string sqlStr)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(globalconnStr))
                using (SqlCommand cmd = new SqlCommand(sqlStr, con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlStr;
                    cmd.Connection.Open();
                    cmd.ExecuteScalar();
                }
            }
            catch
            {
                //eat exception
            }
        }
        public DataTable ExecuteDataTable(string sqlStr)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(globalconnStr))
                using (SqlCommand cmd = new SqlCommand(sqlStr, con))
                {
                    DataTable dt = new DataTable();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlStr;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.Fill(dt);
                    return dt;

                }
            }
            catch
            {
                return null;
            }
        }
        public DataSet ExecuteDataSet(string sqlStr)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(globalconnStr))
                using (SqlCommand cmd = new SqlCommand(sqlStr, con))
                {
                    DataSet ds = new DataSet();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlStr;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.Fill(ds);
                    return ds;

                }
            }
            catch
            {
                return null;
            }
        }

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, that is what I would do.
use a central module or a static class.

I have just added small changes to the code, mainly to the one ExecuteScalar to return the value

 public void ExecuteSql(string sqlStr)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(globalconnStr))
                using (SqlCommand cmd = new SqlCommand(sqlStr, con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlStr;
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch
            {
                //eat exception
            }
        }
        public object ExecuteScalar(string sqlStr)
        {
            object result = null;
            try
            {
                using (SqlConnection con = new SqlConnection(globalconnStr))
                using (SqlCommand cmd = new SqlCommand(sqlStr, con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlStr;
                    cmd.Connection.Open();
                    result = cmd.ExecuteScalar();
                }
            }
            catch
            {
                //eat exception
            }
            return result;
        }
        public DataTable ExecuteDataTable(string sqlStr)
        {
            DataTable dt =new DataTable();
            try
            {
                using (SqlConnection con = new SqlConnection(globalconnStr))
                using (SqlCommand cmd = new SqlCommand(sqlStr, con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlStr;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.Fill(dt);
                }
            }
            catch
            {
                dr = null;
            }
           return dt;
        }
        public DataSet ExecuteDataSet(string sqlStr)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection con = new SqlConnection(globalconnStr))
                using (SqlCommand cmd = new SqlCommand(sqlStr, con))
                {                    
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlStr;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.Fill(ds);
                }
            }
            catch
            {
                ds = null;
            }
           return ds;
        }    

Open in new window

0
r3nderAuthor Commented:
Thanks Guy I appreciate the help the only thing is I am trying to figure out how to do the same thing for a datareader?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
public SqlDataReader ExecuteSql(string sqlStr)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(globalconnStr))
                using (SqlCommand cmd = new SqlCommand(sqlStr, con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlStr;
                    cmd.Connection.Open();
                    return cmd.ExecuteReader();
                }
            }
            catch
            {
                //eat exception
                return null;
            }
        }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
r3nderAuthor Commented:
so after that I would manually assign the reader to the struct as normal?
0
r3nderAuthor Commented:
as in
SqlDataReader reader = ExecuteSql(sqlStr)
while reader.Read())
{
///blah blah

}
0
r3nderAuthor Commented:
Thanks Guy!
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, and glad I could help
0
Carl TawnSystems and Integration DeveloperCommented:
Just as an aside; I would generally be wary of code that delegates the closing of a connection between the caller and the callee. You would generally want the code that opens the connection as close to the code that opens it as possible.

If you have to separate it like you are planning then I would suggest at least specifying the CommandBehaviour.CloseConnection flag when executing the reader.
0
r3nderAuthor Commented:
I understand what you are saying Carl but the code that opens it is the code that closes it by use on the using statements - the only difference is that my other class is basically a string - here is an example
        DTO dto = new DTO();
        public bool InsertUpdateSurvey(string mode, List<DTOV.surveyList> alist)
        {
            bool result = false;
            DateTime SurveyDateTime = Convert.ToDateTime("1900-01-01 12:00:00 000");
            float SurveyDepth = 0;
            float Inclination = 0;
            float Azimuth = 0;
            float TotalGravityField = 0;
            float DIPAngle = 0;
            int SurveyUID = 0;
            int JobID = 0;
            int HoursLeft = 0;
            int BitTripNumber = 0;
            int AvgPulseSize = 0;
            string SurveyStatus = string.Empty;
            string IncParity = string.Empty;


            for (int i = 0; i < alist.Count; i++)
            {
                SurveyUID = Convert.ToInt32(alist[i].SurveyUID.ToString());
                JobID = Convert.ToInt32(alist[i].JobID.ToString());
                SurveyDateTime = Convert.ToDateTime(alist[i].SurveyDateTime.ToString());
                SurveyDepth = float.Parse(alist[i].SurveyDepth.ToString(), CultureInfo.InvariantCulture.NumberFormat);
                Inclination = float.Parse(alist[i].Inclination.ToString(), CultureInfo.InvariantCulture.NumberFormat);
                Azimuth = float.Parse(alist[i].Azimuth.ToString(), CultureInfo.InvariantCulture.NumberFormat);
                TotalGravityField = float.Parse(alist[i].TotalGravityField.ToString(), CultureInfo.InvariantCulture.NumberFormat);
                DIPAngle = float.Parse(alist[i].DIPAngle.ToString(), CultureInfo.InvariantCulture.NumberFormat);
                HoursLeft = Convert.ToInt32(alist[i].HoursLeft.ToString());
                BitTripNumber = Convert.ToInt32(alist[i].BitTripNumber.ToString());
                SurveyStatus = alist[i].SurveyStatus.ToString();
                IncParity = alist[i].IncParity.ToString();
                AvgPulseSize = Convert.ToInt32(alist[i].AvgPulseSize.ToString());
            }

            string sqlStr = string.Empty;
            if (mode == "I")
            {
                sqlStr += "INSERT INTO Surveys(JobID,SurveyDateTime,SurveyDepth,Inclination,Azimuth,TotalGravityField,DIPAngle,HoursLeft,BitTripNumber,SurveyStatus,IncParity,AvgPulseSize) ";
                sqlStr += " values('" + JobID + "','" + SurveyDateTime + "'," + SurveyDepth + "," + Inclination + "," + Azimuth + "," + TotalGravityField + "," + DIPAngle + "," + HoursLeft + "," + BitTripNumber + ",'" + SurveyStatus + "','" + IncParity + "'," + AvgPulseSize + ")";
            }
            if (mode == "U")
            {
                sqlStr += "UPDATE Surveys SET JobID = '" + JobID + "', ";
                sqlStr +=" SurveyDateTime=   '" + SurveyDateTime + "', ";
                sqlStr += " SurveyDepth= " + SurveyDepth + ", ";
                sqlStr += " Inclination= " + Inclination + ", ";
                sqlStr += " Azimuth= " + Azimuth + ", ";
                sqlStr += " TotalGravistyField= " + TotalGravityField + ", ";
                sqlStr += " DIPAngle = " + DIPAngle + ", ";
                sqlStr += " HoursLeft= " + HoursLeft + ", ";
                sqlStr += " BitTripNumber= " + BitTripNumber + ", ";
                sqlStr += " SurveyStatus = '" + SurveyStatus + "', ";
                sqlStr += " IncParity= '" + IncParity + "', ";
                sqlStr += " AvgPulseSize = " + AvgPulseSize + " ";
                sqlStr += " WHERE SurveyUID = "+ SurveyUID +" ";
            }
            try
            {
                dto.ExecuteSql(sqlStr);
                result = true;
            }
            catch
            {
                result = false;
            }

            return result;
        }

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.