public String DataReader2XmlString(IDataReader reader, string tablename)
{
StringBuilder strBuilder = new StringBuilder();
try
{
string tableName = Metin.GecerliMi(tablename) ? tablename : table;
strBuilder.AppendLine("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
strBuilder.AppendFormat("<{0}>\n", tableName + "s");
while (reader.Read())
{
strBuilder.AppendFormat("<{0}>\n", tableName);
for (int i = 0; i < reader.FieldCount; i++)
{
strBuilder.AppendFormat("<{0}>{1}</{2}>\n",
reader.GetName(i), reader.GetString(i), reader.GetName(i));
}
strBuilder.AppendFormat("</{0}>\n", tableName);
}
strBuilder.AppendFormat("</{0}>\n", tableName + "s");
}
catch (Exception)
{
strBuilder = new StringBuilder();
strBuilder.AppendLine("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
}
return(strBuilder.ToString());
}
/// <summary>
///
/// </summary>
/// <param name="reader">DataReader nesnesi</param>
/// <param name="tablename">Xml de gösterilecek tablo ismi</param>
/// <returns>Xml dosyası döndürür.</returns>
public XmlDocument DataReader2Xml(IDataReader reader, string tablename)
{
XmlDocument doc = null;
try
{
doc = new XmlDocument();
XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
doc.AppendChild(docNode);
string tableName = Metin.GecerliMi(tablename) ? tablename : table;
XmlNode rootNode = doc.CreateElement(tableName + "s");
doc.AppendChild(rootNode);
while (reader.Read())
{
XmlNode rowNode = doc.CreateElement(tableName);
for (int i = 0; i < reader.FieldCount; i++)
{
XmlNode colNode = doc.CreateElement(reader.GetName(i));
colNode.InnerText = reader.GetString(i);
rowNode.AppendChild(colNode);
}
rootNode.AppendChild(rowNode);
}
}
catch (Exception)
{
doc = null;
}
return(doc);
}
public DataSet GetDataSetWithConnection(IDbConnection Conn,
CommandType cmdType, string QueryOrProcedure, params Param[] parameters)
{
DataSet dS = new DataSet();
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
using (IDbTransaction dbTrans = Conn.BeginTransaction())
{
try
{
using (IDbCommand dbCmd = Conn.CreateCommand())
{
dbCmd.Transaction = dbTrans;
dbCmd.CommandText = QueryOrProcedure;
dbCmd.CommandType = cmdType;
foreach (Param p in parameters)
{
if (!Metin.GecerliMi(p.Name))
{
throw new Exception("Parameter name could not be empty or null.");
}
IDataParameter dbParameter =
GetParameter(Conn);
dbParameter.ParameterName = p.Name;
dbParameter.Value = p.Value;
dbCmd.Parameters.Add(dbParameter);
}
IDataAdapter dbAdapter = GetDataAdapter(Conn);
dbAdapter.Fill(dS);
dbTrans.Commit();
dbCmd.Parameters.Clear();
} // end dbcmd
} // end try
catch (Exception exc)
{
dbTrans.Rollback();
throw exc;
}
finally
{
Conn.Close();
GC.Collect();
}
} // end dbTrans
return(dS);
}
/// <summary>
///
/// </summary>
/// <param name="connectionstring">Connection String</param>
/// <param name="procedurename">Query</param>
/// <param name="parameters">Paramaters array as Param type</param>
/// <see cref="tr.mustaliscl.data.Param"/>
/// <returns>Returns a DataSet with given parameters</returns>
public DataSet GiveDataSetWithGivenProcedureAndParameters(
ConnectionTypes conType,
string ConnStr, string procedurename, params Param[] parameters)
{
DataSet dS = new DataSet();
using (IDbConnection dbConn = GetConnection(conType))
{
dbConn.ConnectionString = ConnStr;
using (IDbTransaction dbTrans = dbConn.BeginTransaction())
{
try
{
using (IDbCommand dbCmd = dbConn.CreateCommand())
{
dbCmd.Transaction = dbTrans;
dbCmd.CommandText = procedurename;
dbCmd.CommandType = CommandType.StoredProcedure;
foreach (Param p in parameters)
{
if (!Metin.GecerliMi(p.Name))
{
throw new Exception("Parameter name could not be empty or null.");
}
IDataParameter dbParameter =
GetParameter(conType);
dbParameter.ParameterName = p.Name;
dbParameter.Value = p.Value;
dbCmd.Parameters.Add(dbParameter);
}
dbConn.Open();
IDataAdapter dbAdapter = GetDataAdapter(conType, dbCmd);
dbAdapter.Fill(dS);
dbTrans.Commit();
dbCmd.Parameters.Clear();
} // end dbcmd
} // end try
catch (Exception exc)
{
dbTrans.Rollback();
throw exc;
}
finally
{
dbConn.Close();
GC.Collect();
}
} // end dbTrans
} // end dbConn
return(dS);
}
public Int32 ExecuteQueryWithGivenParameters(ConnectionTypes conType,
string ConnStr, string query, params Param[] parameters)
{
Int32 willBeReturn;
using (IDbConnection dbConn = GetConnection(conType))
{
dbConn.ConnectionString = ConnStr;
using (IDbTransaction dbTrans = dbConn.BeginTransaction())
{
try
{
using (IDbCommand dbCmd = dbConn.CreateCommand())
{
dbCmd.Transaction = dbTrans;
dbCmd.CommandText = query;
dbCmd.CommandType = CommandType.Text;
foreach (Param p in parameters)
{
if (!Metin.GecerliMi(p.Name))
{
throw new Exception("Parameter name could not be empty or null.");
}
IDataParameter dbParameter = GetParameter(conType);
dbParameter.ParameterName = p.Name;
dbParameter.Value = p.Value;
dbCmd.Parameters.Add(dbParameter);
}
dbConn.Open();
willBeReturn = dbCmd.ExecuteNonQuery();
dbTrans.Commit();
dbCmd.Parameters.Clear();
dbConn.Close();
} // end dbcmd
} // end try
catch (Exception exc)
{
dbTrans.Rollback();
throw exc;
}
finally
{
dbConn.Close();
GC.Collect();
}
} // end dbTrans
} // end dbConn
return(willBeReturn);
}
/// <summary>
///
/// </summary>
/// <param name="connectionstring">Connection String</param>
/// <param name="procedurename">Query</param>
/// <param name="parameters">Paramaters array as Param type</param>
/// <see cref="tr.mustaliscl.data.Param"/>
/// <returns>Returns a DataSet with given parameters</returns>
public DataSet GetDataSetWithGivenQuery(string ConnStr, string query, params Param[] parameters)
{
DataSet dS = new DataSet();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
if (parameters != null)
{
foreach (Param p in parameters)
{
if (Metin.GecerliMi(p.Name))
{
cmd.Parameters.AddWithValue(p.Name, p.Value);
}
else
{
throw new Exception();
}
}
}
//conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dS);
trans.Commit();
cmd.Parameters.Clear();
} // end sql cmd
}
catch (Exception exc)
{
trans.Rollback();
throw exc;
}
finally
{
conn.Close();
SqlConnection.ClearPool(conn);
GC.Collect();
}
}// end trans
}
return(dS);
}// end GetDataSetWithGivenQuery method
}// end GetDataSetWithGivenQuery method
#endregion
#region [Get DataSet With Given Procedure And Parameters]
/// <summary>
///
/// </summary>
/// <param name="ConnStr">Connection String</param>
/// <param name="procedurename">Procedure Name will be used</param>
/// <param name="parameters">Paramaters array as Param type</param>
/// <see cref="tr.mustaliscl.data.Param"/>
/// <returns>Returns a DataSet with given parameters</returns>
public DataSet GetDataSetWithGivenProcedureAndParameters(string ConnStr, string procedurename, params Param[] parameters)
{
DataSet dS = new DataSet();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = procedurename;
cmd.CommandType = CommandType.StoredProcedure;
foreach (Param p in parameters)
{
if (Metin.GecerliMi(p.Name))
{
cmd.Parameters.AddWithValue(p.Name, p.Value);
}
else
{
throw new Exception();
}
}
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dS);
trans.Commit();
adapter.Dispose();
cmd.Parameters.Clear();
} // end sql cmd
}
catch (Exception exc)
{
trans.Rollback();
throw exc;
}
finally
{
conn.Close();
GC.Collect();
}
}// end trans
SqlConnection.ClearPool(conn);
}
return(dS);
} // end GiveDataSetWithGivenProcedureAndParameters method