Omgå CLR spørgsmål ifm. connection til ms sql database
Hej,Da jeg startede ud med at tilgå sql database, fik jeg i tidernes morgen fejl at clr ikke var enabled....Dette gjorde jeg så - ingen ko på isen der....Og nej, jeg tænkte ikke en tanke om hvorfor, hvordan og hvorledes....
Jeg har aldrig haft problemet de steder, jeg har arbejdet (der har den højst sansynligt været enabled)
Nu er jeg kommet i den sitation, at jeg har lavet et fint program, som skal køre på UnoEuro.com.....og de er ikke til at hugge og stikke i.
Rigtig gættet - nu får jeg fejlen igen....Nu må jeg så ændre min kode, så det ikke er nødvendigt med at enable clr....men jeg har ingen anelse om, hvad jeg skal kigge efter.
Nogen der kan sige mig, hvor det går galt i min kode og hvad jeg skal rette det til?
Min DBUtility:
namespace AnsiBase.DAL
{
/// <summary>
/// DBUtility is a class that takes the common data classes and allows you
/// to execute commands, add parameters, and return datasets and datatables.
/// </summary>
///
public class DBUtility
{
#region " Private Members "
private string _connectionstring = "";
private SqlConnection _connection;
private SqlCommand _command;
private int _commandTimeout = 30;
#endregion
public DBUtility()
{
try
{
_connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["AnsiDB"].ToString();
_connection = new SqlConnection(_connectionstring);
_command = new SqlCommand();
_command.CommandTimeout = _commandTimeout;
_command.Connection = _connection;
}
catch(Exception ex)
{
throw new Exception("Error initializing data class. " + Environment.NewLine + ex.Message);
}
}
# region " Methods "
/// <summary>
/// Clean up Connection and Command object
/// </summary>
private void Dispose()
{
try
{
//Clean up Connection Object
if (_connection != null)
{
if (_connection.State != ConnectionState.Closed)
{
_connection.Close();
}
_connection.Dispose();
}
//Clean up Command Object
if (_command != null)
{
_command.Dispose();
}
}
catch(Exception ex)
{
throw new Exception("Error disposing data class." + Environment.NewLine + ex.Message);
}
}
#region " Parameters "
/// <summary>
/// Creates a parameter and adds it to the command object
/// </summary>
/// <param name="name">The parameter name</param>
/// <param name="value">The paremeter value</param>
public void AddParameter(string name, object value)
{
try
{
SqlParameter parm = new SqlParameter();
parm.ParameterName = name;
parm.Value = value;
_command.Parameters.Add(parm);
}
catch(Exception)
{
throw;
}
}
/// <summary>
/// Creates a parameter and adds it to the command object
/// </summary>
/// <param name="name">The parameter name</param>
/// <param name="value">The paremeter type</param>
/// <param name="size">The paremeter size</param>
public void AddParameter(string name, SqlDbType type, object value)
{
try
{
SqlParameter parm = new SqlParameter();
parm.ParameterName = name;
parm.Value = value;
parm.SqlDbType = type;
_command.Parameters.Add(parm);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Set the value of parameter
/// </summary>
/// <param value="value">The parameter value</param>
public void SetParameterValue(string name, object value)
{
try
{
_command.Parameters[name].Value = value;
}
catch(Exception)
{
throw;
}
}
#endregion
#region " Transactions "
/// <summary>
/// Starts a transaction for the command object
/// </summary>
private void BeginTransaction()
{
if (_connection.State == System.Data.ConnectionState.Closed)
{
_connection.Open();
}
_command.Transaction = _connection.BeginTransaction();
}
/// <summary>
/// Commits a transaction for the command object
/// </summary>
private void CommitTransaction()
{
_command.Transaction.Commit();
_connection.Close();
}
/// <summary>
/// Rolls back the transaction for the command object
/// </summary>
private void RollbackTransaction()
{
_command.Transaction.Rollback();
_connection.Close();
}
#endregion
#region " Execute database functions "
/// <summary>
/// Executes a statement that does not return a result set, such as an INSERT, UPDATE, DELETE, or a data definition statement
/// </summary>
/// <param name="query">The query as StoredProcedure</param>
public void ExecuteNonQuery(string query, CommandType commandtype)
{
_command.CommandText = query;
_command.CommandTimeout = _commandTimeout;
_command.CommandType = commandtype;
try
{
if (_connection.State == System.Data.ConnectionState.Closed)
{
_connection.Open();
}
_command.Connection = _connection;
BeginTransaction();
_command.ExecuteNonQuery();
CommitTransaction();
}
catch (Exception)
{
RollbackTransaction();
throw;
}
finally
{
_command.Parameters.Clear();
if (_connection.State == System.Data.ConnectionState.Open)
{
Dispose();
}
}
}
/// <summary>
/// Executes a statement that returns a single value.
/// If this method is called on a query that returns multiple rows and columns, only the first column of the first row is returned.
/// </summary>
/// <param name="query">The query as StoredProcedure</param>
/// <returns>An integer value</returns>
public int ExecuteScaler(string query, CommandType commandtype)
{
object identity = 0;
try
{
_command.CommandText = query;
_command.CommandTimeout = _commandTimeout;
_command.CommandType = commandtype;
if (_connection.State == System.Data.ConnectionState.Closed)
{
_connection.Open();
}
_command.Connection = _connection;
BeginTransaction();
identity = _command.ExecuteScalar();
CommitTransaction();
}
catch (Exception)
{
RollbackTransaction();
throw;
}
finally
{
_command.Parameters.Clear();
if (_connection.State == System.Data.ConnectionState.Open)
{
Dispose();
}
}
return Convert.ToInt32(identity);
}
/// <summary>
/// Executes a SQL statement that returns a result set.
/// </summary>
/// <param name="query">The query, either SQL or Procedures</param>
/// <returns>A datareader object</returns>
public SqlDataReader ExecuteReader(string query)
{
//command.CommandText = query;
//command.CommandType = commandtype;
SqlDataReader reader = null;
try
{
if (_connection.State == System.Data.ConnectionState.Closed)
{
_connection.Open();
}
SqlCommand myCommand = new SqlCommand(query, _connection);
reader = myCommand.ExecuteReader();
}
catch (Exception)
{
throw;
}
finally
{
_command.Parameters.Clear();
if (_connection.State == System.Data.ConnectionState.Open)
{
Dispose();
}
}
return reader;
}
/// <summary>
/// Generates a dataset
/// </summary>
/// <param name="query">The query, either SQL or Procedures</param>
/// <param name="commandtype">The command type, text, storedprocedure, or tabledirect</param>
/// <param name="connectionstate">The connection state</param>
/// <returns>A dataset containing data from the database</returns>
public DataSet GetDataSet(string query, CommandType commandtype)
{
_command.CommandText = query;
_command.CommandTimeout = _commandTimeout;
_command.CommandType = commandtype;
SqlDataAdapter adpt = new SqlDataAdapter(_command);
DataSet ds = new DataSet();
try
{
if (_connection.State == System.Data.ConnectionState.Closed)
{
_connection.Open();
}
adpt.Fill(ds);
}
catch (Exception)
{
throw;
}
finally
{
_command.Parameters.Clear();
if (_connection.State == System.Data.ConnectionState.Open)
{
Dispose();
}
}
return ds;
}
/// <summary>
/// Generates a datatable
/// </summary>
/// <param name="query">The query, either SQL or Procedures</param>
/// <param name="commandtype">The command type, text, storedprocedure, or tabledirect</param>
/// <param name="connectionstate">The connection state</param>
/// <returns>A dataset containing data from the database</returns>
public DataTable GetDataTable(string query, CommandType commandtype)
{
_command.CommandText = query;
_command.CommandTimeout = _commandTimeout;
_command.CommandType = commandtype;
SqlDataAdapter adpt = new SqlDataAdapter(_command);
DataSet ds = new DataSet();
try
{
if (_connection.State == System.Data.ConnectionState.Closed)
{
_connection.Open();
}
adpt.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_command.Parameters.Clear();
if (_connection.State == System.Data.ConnectionState.Open)
{
Dispose();
}
}
return ds.Tables[0];
}
#endregion
#endregion
#region enums
/// <summary>
/// A list of data providers
/// </summary>
public enum Providers
{
SqlServer,
OleDB,
ODBC,
Oracle,
MySQL
}
#endregion
}
}
-------------------------------
Min klasse, hvor jeg laver kaldet:
namespace Anja.Libriary
{
public class Test
{
#region " Enums, Members, Properties "
DBUtility DAL = new DBUtility();
//public string bugName { get; set; }
#endregion
#region " Data Access Get "
public DataTable GetTest()
{
//DAL.AddParameter("bugId", bugId);
DataTable getData = DAL.GetDataTable("XXTestSelectUsersXX", CommandType.StoredProcedure);
return getData;
}
#endregion
}
}
--------------------
Her bruger jeg så kode:
protected void Page_Load(object sender, EventArgs e)
{
Anja.Libriary.Test test = new Anja.Libriary.Test();
DataTable getData = test.GetTest();
ddlTest.DataSource = getData.DefaultView;
ddlTest.DataBind();
}
mvh
simsen :-)
