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?
 
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
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.