How to write sql queries in txt file c# transaction

I want to write sql queries in txt file c# transaction. I am able to write sql queries in txt file in normal situation.

Sql query save to txt file successfullyas per my own class

string strSql = "insert into dbo.repayment_collection_master( AccId, productId, mem_code, memberName)Values('1','1','3','Prasanta')

DataAccess.ExecuteNonQuery(strSql);
public static void ExecuteNonQuery(String sql)
{
SqlConnection con = DBConnection.Connection;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = con;
cmd.CommandTimeout = 0;
if (con.State != ConnectionState.Open)
con.Open();
int i = 0;
i= cmd.ExecuteNonQuery();
if (i != 0)
{
addQueryToDb ad = new addQueryToDb(sql);
}
con.Close();
}`
public addQueryToDb(string str)
{
string LogFileName = string.Empty;
string userName = "work_";
LogFileName = DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString();
LogFileName = HttpContext.Current.Server.MapPath("~/userWork\\" + userName + "" + LogFileName + ".txt");
FileStream fs = new FileStream(LogFileName, FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter m_streamWriter = new StreamWriter(fs);
m_streamWriter.BaseStream.Seek(0, SeekOrigin.End);
m_streamWriter.WriteLine(str);
m_streamWriter.WriteLine("\n");
m_streamWriter.Flush();
m_streamWriter.Close();
fs.Dispose();
m_streamWriter.Dispose();
}

But I am not able to write query from C# transaction

command.CommandText = @"insert into dbo.repayment_collection_master( AccId, productId, mem_code, memberName)Values('1','1','3','Prasanta')"; command.ExecuteNonQuery();
command = new SqlCommand("SP_Loan_Repayment", connection, transaction); command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@branchId", SqlDbType.VarChar).Value = branchId;
command.Parameters.Add("@centerId", SqlDbType.VarChar).Value = centerId;
command.Parameters.Add("@centerName", SqlDbType.VarChar).Value = centerName;
command.Parameters.Add("@repayDate", SqlDbType.VarChar).Value = repayDate;
command.Parameters.Add("@repayPrinc", SqlDbType.Decimal).Value = repayPrinc;
command.Parameters.Add("@repayInt", SqlDbType.Decimal).Value = repayInt;
command.Parameters.Add("@transactionId", SqlDbType.VarChar).Value = transactionId;
command.Parameters.Add("@approvedBy", SqlDbType.VarChar).Value = approvedBy;
SqlParameter VoucharReset = command.Parameters.Add("@VoucharReset", SqlDbType.Int, 2);
VoucharReset.Direction = ParameterDirection.Output;
SqlParameter ReturnIndex = command.Parameters.Add("@ReturnIndex", SqlDbType.Int, 2);
ReturnIndex.Direction = ParameterDirection.Output;
SqlParameter ReturnMsg = command.Parameters.Add("@ReturnMsg", SqlDbType.VarChar, 4000);
ReturnMsg.Direction = ParameterDirection.Output;
SqlParameter ReturntransactionId = command.Parameters.Add("@ReturntransactionId", SqlDbType.VarChar, 10);
ReturntransactionId.Direction = ParameterDirection.Output;
command.ExecuteNonQuery(); transaction.Commit();
After transaction.Commit() //I want to write above query to txt file

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vinay Singh6y ago
hi it's simple just create a text log file day wise. and put all the query In it. have a look of the log file creation in text file https://stackoverflow.com/questions/20185015/how-to-write-log-file-in-c
0
Prasanta Baidya6y ago
I have maintained a daily text file for saving all the query executed from page. Because if the database any day it will corrupt, then restore last backup and executed all the query day by day as they saved.
0
Vinay Singh6y ago
hi ur query is not clear plz clear it
0