ado.net

[grakaman]

wie kann ich am besten unter asp.net (c#) wiederverwertbare connections kreieren? ich möchte nicht ständig die lästigen zeilen mit dem connectionstring schreiben. die zeiten mit include files sind ja nun vorbei bzw. das ist nicht gerade die feine art, wie man programmieren sollte. ich habe oft etwas von connection pooling gehört, aber so recht kann ich keine informationen dazu finden. oder die connection einfach in eine klasse reinschreiben scheint ja auch nicht so recht zu funktionieren, weil dann im command object als übergabeparameter wohl kein klassenverweis zugelassen ist oder was weiss ich... :D

mfg
graka

[Wudu]

?? warum sollt das ned gehen, ich verwende eine solche Classe für die Datenbank in C#, abber die hab ich ned geschrieben, dafür bin ich zu sehr .NET Nuub!

[grakaman]

habe schon ne antwort gefunden. den connection string deklariere ich in der web.config
trotzdem wäre es cool, wenn du mal den code von der klasse postest und gleichzeitig, wie du sie dann auf andere seiten verwendest.

mfg
tetra

[Wudu]

das is die classe
die holt noch settings aus meiner configuration.cs


using System;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.Reflection;
using System.Globalization;
using System.Collections;


namespace DepartmentMembers.Framework
{
/// <summary>
/// Summary description for DataAccess.
/// </summary>
public class DataAccess
{
private static OleDbConnection m_interalConnection = null; // static connection object
private static SqlConnection m_interalSqlConnection = null; // static connection object
private static DataTable m_internalStatusTable = null;

public DataAccess()
{
m_interalConnection = null; // set the connection to null
m_interalSqlConnection = null; // set the connection to null
m_internalStatusTable = null;

CreateStatusTable();
}

public static bool CreateStatusTable()
{
m_internalStatusTable = new DataTable("Status");

DataColumn myColumn;

myColumn = new DataColumn();
myColumn.DataType = System.Type.GetType("System.Int32");
myColumn.ColumnName = "StatusCode";
myColumn.ReadOnly = false;
myColumn.Unique = false;

m_internalStatusTable.Columns.Add(myColumn);

myColumn = new DataColumn();
myColumn.DataType = System.Type.GetType("System.String");
myColumn.ColumnName = "Status";
myColumn.ReadOnly = false;
myColumn.Unique = false;

m_internalStatusTable.Columns.Add(myColumn);

myColumn = new DataColumn();
myColumn.DataType = System.Type.GetType("System.String");
myColumn.ColumnName = "Source";
myColumn.ReadOnly = false;
myColumn.Unique = false;

m_internalStatusTable.Columns.Add(myColumn);

myColumn = new DataColumn();
myColumn.DataType = System.Type.GetType("System.String");
myColumn.ColumnName = "Description";
myColumn.ReadOnly = false;
myColumn.Unique = false;

m_internalStatusTable.Columns.Add(myColumn);

myColumn = new DataColumn();
myColumn.DataType = System.Type.GetType("System.String");
myColumn.ColumnName = "StackTrace";
myColumn.ReadOnly = false;
myColumn.Unique = false;

m_internalStatusTable.Columns.Add(myColumn);

return true;
}

public static DataSet AppendStatusInfo(DataSet resultSet, int nSucceed, string strStatus, string strSource, string strStackTrace, string strDescription)
{
DataSet setReturn;

DataRow statusRow;

m_internalStatusTable = null;
CreateStatusTable();

if(resultSet == null)
{
setReturn = new DataSet();

m_internalStatusTable.Rows.Clear();

statusRow = m_internalStatusTable.NewRow();

statusRow["StatusCode"] = nSucceed;
statusRow["Status"] = strStatus;
statusRow["Description"] = strDescription;
statusRow["Source"] = strSource;
statusRow["StackTrace"] = strStackTrace;

m_internalStatusTable.Rows.Add(statusRow);

setReturn.Tables.Add(m_internalStatusTable);

return setReturn;
}

m_internalStatusTable.Rows.Clear();

statusRow = m_internalStatusTable.NewRow();

statusRow["StatusCode"] = nSucceed;
statusRow["Status"] = strStatus;
statusRow["Description"] = strDescription;
statusRow["Source"] = strSource;
statusRow["StackTrace"] = strStackTrace;

m_internalStatusTable.Rows.Add(statusRow);

resultSet.Tables.Add(m_internalStatusTable);

return resultSet;
}

public static bool Succeed(DataSet dsStatus)
{
if(dsStatus.Tables.Contains("Status") == false)
return true;

if(Int32.Parse(dsStatus.Tables["Status"].Rows[0]["StatusCode"].ToString()) != 0 )
return true;

return false;
}

public static string GetErrorDescription(DataSet dsStatus)
{
if(dsStatus.Tables.Contains("Status") == false)
return "";

return dsStatus.Tables["Status"].Rows[0]["Description"].ToString();
}

public static string GetErrorSource(DataSet dsStatus)
{
if(dsStatus.Tables.Contains("Status") == false)
return "";

return dsStatus.Tables["Status"].Rows[0]["Source"].ToString();
}

public static string GetErrorStackTrace(DataSet dsStatus)
{
if(dsStatus.Tables.Contains("Status") == false)
return "";

return dsStatus.Tables["Status"].Rows[0]["StackTrace"].ToString();
}

public static DataSet ConnectToDb()
{
if((Configuration.Settings.DatabaseProperties.ConnectionType == DBConnTypes.MSAccess)||(Configuration.Settings.DatabaseProperties.ConnectionType == DBConnTypes.NativeOleDB))
{
// If we're already connected
if(m_interalConnection != null)
{
// return true
return AppendStatusInfo(null,1,"Succeed","","","");
}

// try to create a new database connection
// using SEH
try
{
m_interalConnection = new OleDbConnection(Configuration.Settings.DatabaseConnectionString);
m_interalConnection.Open();
}
catch (Exception e)
{
// if the creation of the new connection fails or
// if the connection couldn't be opened
// return false
m_interalConnection = null;
return AppendStatusInfo(null,0,"Failed",e.Source,e.StackTrace,e.Message);
}

// database connection should now be opened
return AppendStatusInfo(null,1,"Succeed","","","");
}
else
{
// If we're already connected
if(m_interalSqlConnection != null)
{
// return true
return AppendStatusInfo(null,1,"Succeed","","","");
}

// try to create a new database connection
// using SEH
try
{
m_interalSqlConnection = new SqlConnection(Configuration.Settings.DatabaseConnectionString);
m_interalSqlConnection.Open();
}
catch (Exception e)
{
// if the creation of the new connection fails or
// if the connection couldn't be opened
// return false
m_interalSqlConnection = null;
return AppendStatusInfo(null,0,"Failed",e.Source,e.StackTrace,e.Message);
}

// database connection should now be opened
return AppendStatusInfo(null,1,"Succeed","","","");
}
}

public static DataSet DisconnectFromDb()
{
if((Configuration.Settings.DatabaseProperties.ConnectionType == DBConnTypes.MSAccess)||(Configuration.Settings.DatabaseProperties.ConnectionType == DBConnTypes.NativeOleDB))
{
// If we're already disconnected
if(m_interalConnection == null)
{
// return true
return AppendStatusInfo(null,1,"Succeed","","","");
}

// try to create a new database connection
// using SEH
try
{
m_interalConnection.Close();
m_interalConnection = null;
}
catch (Exception e)
{
// if the connection can't be closed at the moment
// return false

return AppendStatusInfo(null,0,"Failed",e.Source,e.StackTrace,e.Message);
}

// database connection should now be closed
return AppendStatusInfo(null,1,"Succeed","","","");
}
else
{
// If we're already disconnected
if(m_interalSqlConnection == null)
{
// return true
return AppendStatusInfo(null,1,"Succeed","","","");
}

// try to create a new database connection
// using SEH
try
{
m_interalSqlConnection.Close();
m_interalSqlConnection = null;
}
catch (Exception e)
{
// if the connection can't be closed at the moment
// return false

return AppendStatusInfo(null,0,"Failed",e.Source,e.StackTrace,e.Message);
}

// database connection should now be closed
return AppendStatusInfo(null,1,"Succeed","","","");
}
}

public static DataSet ExecuteQuery(String strQueryString)
{
if((Configuration.Settings.DatabaseProperties.ConnectionType == DBConnTypes.MSAccess)||(Configuration.Settings.DatabaseProperties.ConnectionType == DBConnTypes.NativeOleDB))
{
DataSet dsRet;

if((m_interalConnection == null)||(m_interalConnection.State != ConnectionState.Open))
{
DataSet dsConn = DataAccess.ConnectToDb();

if(!DataAccess.Succeed(dsConn))
return dsConn;
}

try
{
OleDbDataAdapter myCommand = new OleDbDataAdapter(strQueryString,m_interalConnection);

dsRet = new DataSet();

myCommand.Fill(dsRet,"QueryResult");
}
catch(Exception e)
{
return AppendStatusInfo(null,0,"Failed",e.Source,e.StackTrace,e.Message);
}

return AppendStatusInfo(dsRet,1,"Succeed","","","");
}
else
{
DataSet dsRet;

if((m_interalSqlConnection == null)||(m_interalSqlConnection.State != ConnectionState.Open))
{
DataSet dsConn = DataAccess.ConnectToDb();

if(!DataAccess.Succeed(dsConn))
return dsConn;
}

try
{
SqlDataAdapter myCommand = new SqlDataAdapter(strQueryString,m_interalSqlConnection);

dsRet = new DataSet();

myCommand.Fill(dsRet,"QueryResult");
}
catch(Exception e)
{
return AppendStatusInfo(null,0,"Failed",e.Source,e.StackTrace,e.Message);
}

return AppendStatusInfo(dsRet,1,"Succeed","","","");
}
}

public static DataSet ExecuteNonQuery(String strNonQueryString)
{
if((Configuration.Settings.DatabaseProperties.ConnectionType == DBConnTypes.MSAccess)||(Configuration.Settings.DatabaseProperties.ConnectionType == DBConnTypes.NativeOleDB))
{
if((m_interalConnection == null)||(m_interalConnection.State != ConnectionState.Open))
{
DataSet dsConn = DataAccess.ConnectToDb();

if(!DataAccess.Succeed(dsConn))
return dsConn;
}

try
{
OleDbCommand myCommand = new OleDbCommand(strNonQueryString,m_interalConnection);

myCommand.ExecuteNonQuery();
}
catch(Exception e)
{
return AppendStatusInfo(null,0,"Failed",e.Source,e.StackTrace,e.Message);
}

return AppendStatusInfo(null,1,"Succeed","","","");
}
else
{
if((m_interalSqlConnection == null)||(m_interalSqlConnection.State != ConnectionState.Open))
{
DataSet dsConn = DataAccess.ConnectToDb();

if(!DataAccess.Succeed(dsConn))
return dsConn;
}

try
{
SqlCommand myCommand = new SqlCommand(strNonQueryString,m_interalSqlConnection);

myCommand.ExecuteNonQuery();
}
catch(Exception e)
{
return AppendStatusInfo(null,0,"Failed",e.Source,e.StackTrace,e.Message);
}

return AppendStatusInfo(null,1,"Succeed","","","");
}
}

public static string TransformSQLData(string stringData)
{
string strRet;

strRet = stringData;

if(strRet != null)
{
strRet = strRet.Replace("'","''");
}

return strRet;
}
}

public enum DBConnTypes
{
MSAccess = 0,
MSSQLServer = 1,
OracleServer = 2, // NOT SUPPORTED IN THE CURRENT VERSION !!!
NativeOleDB = 3,
NativeODBC = 4 // NOT SUPPORTED IN THE CURRENT VERSION !!!
}

public class DBProperties
{
private string _connectionString;
private DBConnTypes _connectionType;
private string _datePrefix;
private string _datePostfix;

public DBProperties()
{

}

[XmlElement]
public string DatabaseConnectionString
{

get
{
return _connectionString;
}
set
{
_connectionString = value;
}
}

[XmlElement]
public DBConnTypes ConnectionType
{

get
{
return _connectionType;
}
set
{
_connectionType = value;
}
}

[XmlElement]
public string DatePrefix
{

get
{
return _datePrefix;
}
set
{
_datePrefix = value;
}
}

[XmlElement]
public string DatePostfix
{

get
{
return _datePostfix;
}
set
{
_datePostfix = value;
}
}
}
}

[Wudu]

verwenden tu ich das ganze dann so


DataAccess.ConnectToDb();

DataSet data = DataAccess.ExecuteQuery(SQLQuery);
DataAccess.DisconnectFromDb();

if(!DataAccess.Succeed(data))
{
Response.Write(DataAccess.GetErrorDescription(data));
}


die Classe ist als Namespace ins Projekt eingebunden!

[grakaman]

danke, ich mach mir gerade ne eigene klasse. allerdings wird halt in deiner data klasse alles verarbeitet, indem du den sqlstring übergibst. aber ich setze ja überwiegend stored procedures ein mit unterschiedlichen parametern und da kann ich nicht für jede eine neue methode in der klasse implementieren. aber ich bekomm das schon irgendwie hin :D

mfg

[Wudu]

hmm, mit stored procedures geht die classe auch, die ist für SQL Server und Access (Jet4).
Wenn du VS.Net verwendest, solltest du noch weniger ein Problem damit haben......

[grakaman]

kann ich mir nicht vorstellen, weil bei stored procedures in ado.net für jeden parameter ein neues object instanziert werden muss.

[Wudu]

ich check das mal mit meinem Programmierguru ab, ich selbst bin noch zu schwach in .NET!