|
Hi to All,
I am using C# Winforms to create a Voucher system which is Master/Detail, using MySql as backend. Create Voucher class to setup followings:
1.Create New Voucher 2. Save Voucher 3. Query Voucher
Currently having problem with Save Voucher, I've setup Insert/Update/Delete commands, to make this post short I am posting Insert commands so that general idea of application will be clear:
Class Variables
ClsMgt da = new ClsMgt();
MySqlDataAdapter sqlDataMaster = new MySqlDataAdapter();
private DataSet oDs = null;
MySqlCommand selectcommand = null;
MySqlCommand insertcommand = null;
MySqlCommand updatecommand = null;
MySqlCommand deletecommand = null;
private DataTable dt = null;
private DataTable dtDet = null;
private String sSelProcName = null;
private String sInsProcName = null;
private String sDelProcName = null;
private String sUpdProcName = null;
private int voucType;
public MySqlConnection oCn = new MySqlConnection();
MySqlTransaction oTrn = null;
Following is the NewVoucher procedure which will setup VoucherForm to open with blank record:
public DataSet NewVoucher()
{
DataSet vDs = new DataSet();
oCn = da.GetConnection();
if (oCn == null)
{
oCn.Open();
}
try
{
DataTable dt = new DataTable();
String sqlSelect = "Select vID, vTypeID, vNo, accCodeDR, accCodeCR, vDate, vChqNo, vChqDt, vPayName, vRemarks, vAmount from vMaster";
sqlDataMaster = new MySqlDataAdapter(sqlSelect, oCn);
sqlDataMaster.FillSchema(dt, SchemaType.Source);
vDs.Tables.Add(dt);
VoucherDetails vdet = new VoucherDetails();
DataTable dtDet = new DataTable();
dtDet = vdet.NewVoucherDet();
vDs.Tables.Add(dtDet);
vDs.Tables[0].Columns["vID"].AutoIncrement = true;
vDs.Tables[0].Columns["vID"].AutoIncrementSeed = -1;
vDs.Tables[0].Columns["vID"].AutoIncrementStep = -1;
vDs.Tables[1].Columns["vID"].AutoIncrement = true;
vDs.Tables[1].Columns["vID"].AutoIncrementSeed = -1;
vDs.Tables[1].Columns["vID"].AutoIncrementStep = -1;
vDs.EnforceConstraints = false;
vDs.Relations.Add("VouchersToVoucherDetails", vDs.Tables[0].Columns["vID"], vDs.Tables[1].Columns["vID"]);
}
catch (MySqlException e)
{
MessageBox.Show(e.ToString());
}
return (vDs);
}
Following is the SaveVoucher procedure:
public void SaveVoucher(DataSet oDs)
{
oCn = da.GetConnection();
oTrn = oCn.BeginTransaction();
sqlDataMaster = new MySqlDataAdapter();
try
{
if (oCn == null)
{
oCn.Open();
}
sInsProcName = "prInsert_Voucher";
insertcommand = new MySqlCommand(sInsProcName, oCn, oTrn);
insertcommand.CommandType = CommandType.StoredProcedure;
insertcommand.Parameters.Add(new MySqlParameter("nNewID", MySqlDbType.Int32, 0, "vID"));
insertcommand.Parameters["nNewID"].Direction = ParameterDirection.Output;
insertcommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
insertcommand.Parameters.Add("svTypeID", MySqlDbType.Int32,0, "vTypeID");
insertcommand.Parameters.Add("svNo", MySqlDbType.Int32, 0, "vNo");
insertcommand.Parameters.Add("svaccCodeDR", MySqlDbType.VarChar, 12, "accCodeDR");
insertcommand.Parameters.Add("svaccCodeCR", MySqlDbType.VarChar, 12, "accCodeCR");
insertcommand.Parameters.Add("svDate", MySqlDbType.DateTime);
insertcommand.Parameters["svDate"].SourceColumn = "vDate";
insertcommand.Parameters.Add("svChqNo", MySqlDbType.Int32, 0, "vChqNo");
insertcommand.Parameters.Add("svChqDt", MySqlDbType.DateTime);
insertcommand.Parameters["svChqDt"].SourceColumn = "vChqDt";
insertcommand.Parameters.Add("svPayName", MySqlDbType.VarChar, 100, "vPayName");
insertcommand.Parameters.Add("svRemarks", MySqlDbType.VarChar, 70, "vRemarks");
insertcommand.Parameters.Add("svAmount", MySqlDbType.Double, 0, "vAmount");
sqlDataMaster.InsertCommand = insertcommand;
sUpdProcName = "prUpdate_Voucher";
updatecommand = new MySqlCommand(sUpdProcName, oCn, oTrn);
updatecommand.CommandType = CommandType.StoredProcedure;
updatecommand.Parameters.Add("nNewID", MySqlDbType.Int32, 4, "vID");
updatecommand.Parameters.Add("svTypeID", MySqlDbType.Int32, 0, "CustomerID");
updatecommand.Parameters.Add("svNo", MySqlDbType.Int32, 0, "vNo");
updatecommand.Parameters.Add("svaccCodeDR", MySqlDbType.VarChar, 12, "accCodeDR");
updatecommand.Parameters.Add("svaccCodeCR", MySqlDbType.VarChar, 12, "accCodeCR");
updatecommand.Parameters.Add("svDate", MySqlDbType.DateTime);
updatecommand.Parameters["svDate"].SourceColumn = "vDate";
updatecommand.Parameters.Add("svChqNo", MySqlDbType.Int32, 0, "vChqNo");
updatecommand.Parameters.Add("svChqDt", MySqlDbType.Date);
updatecommand.Parameters["svChqDt"].SourceColumn = "vChqDt";
updatecommand.Parameters.Add("svPayName", MySqlDbType.VarChar, 100, "vPayName");
updatecommand.Parameters.Add("svRemarks", MySqlDbType.VarChar, 70, "vRemarks");
updatecommand.Parameters.Add("svAmount", MySqlDbType.Double, 0, "vAmount");
sqlDataMaster.UpdateCommand = updatecommand;
sDelProcName = "prDelete_Voucher";
deletecommand = new MySqlCommand(sDelProcName, oCn, oTrn);
deletecommand.CommandType = CommandType.StoredProcedure;
deletecommand.Parameters.Add("nNewID", MySqlDbType.Int32, 4, "vID");
sqlDataMaster.DeleteCommand = deletecommand;
sqlDataMaster.Update(oDs.Tables[0]);
VoucherDetails vDet = new VoucherDetails();
vDet.SaveVoucher(oTrn, oDs.Tables[1]);
oTrn.Commit();
MessageBox.Show("Records saved.", "Saving Records");
}
catch (MySqlException e)
{
oTrn.Rollback();
MessageBox.Show(e.Message +" Error code: "+ e.Number);
oDs.Tables[0].Rows[0]["vNo"] = DBNull.Value;
}
}
Using paramterized StoredProc to save data in MySql db, for Insert records following StoredProc is used:
DELIMITER $$
USE `generalledger`$$
DROP PROCEDURE IF EXISTS `prInsert_Voucher`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `prInsert_Voucher`(
IN svTypeID INT,
IN svNo INT,
IN svaccCodeDR VARCHAR(12),
IN svaccCodeCR VARCHAR(12),
IN svDate DATETIME,
IN svChqNo INT,
IN svChqDt DATETIME,
IN svPayName VARCHAR(100),
IN svRemarks VARCHAR(70),
IN svAmount DOUBLE,
OUT nNewID INT)
BEGIN
INSERT INTO vMaster (vTypeID, vNo, accCodeDR,accCodeCR,vDate, vChqNo, vChqDt, vPayName, vRemarks, vAmount)
VALUES (svTypeID, svNo, svaccCodeDR, svaccCodeCR, svDate, svChqNo, svChqDt, svPayName, svRemarks, svAmount);
SET nNewID = LAST_INSERT_ID();
END$$
DELIMITER ;
Code behind Voucher form:
public frmVoucher()
{
InitializeComponent();
DoNewVoucher();
}
private void Form1_Load(object sender, EventArgs e)
{
txtVDt.Text = DateTime.Today.ToString();
}
public void DoNewVoucher()
{
vr.VoucherType = 1;
ds = vr.NewVoucher();
bs = new BindingSource();
bsDet = new BindingSource();
bs.DataSource = ds;
bs.DataMember = "vMaster";
bsDet.DataSource = bs;
bsDet.DataMember = "VouchersToVoucherDetails";
BindControls();
bs.AddNew();
txtVDt.Text = DateTime.Today.ToString();
}
public void BindControls()
{
txtvTypeID.Text = null;
txtVType.Text = null;
txtVNum.Text = null;
txtVDesc.Text = null;
txtVDt.Text = null;
if (vr.VoucherType == 1)
{
txtChqNo.Text = null;
txtChqDt.Text = null;
txtDrAcc.Text = null;
txtDrAccDesc.Text = null;
txtCrAcc.Text = null;
txtCrAccDesc.Text = null;
txtPayName.Text = null;
txtAmount.Text = null;
}
txtRemarks.Text = null;
txtTransID.DataBindings.Clear();
txtvTypeID.DataBindings.Clear();
txtVType.DataBindings.Clear();
txtVNum.DataBindings.Clear();
txtVDt.DataBindings.Clear();
if (vr.VoucherType == 1)
{
txtChqNo.DataBindings.Clear();
txtChqDt.DataBindings.Clear();
txtDrAcc.DataBindings.Clear();
txtCrAcc.DataBindings.Clear();
txtPayName.DataBindings.Clear();
txtAmount.DataBindings.Clear();
}
txtRemarks.DataBindings.Clear();
txtTransID.DataBindings.Add(new Binding("Text", bs, "vID"));
txtvTypeID.DataBindings.Add(new Binding("Text", bs, "vTypeID"));
txtVNum.DataBindings.Add(new Binding("Text", bs, "vNo"));
txtVDt.DataBindings.Add(new Binding("Text", bs, "vDate"));
if (vr.VoucherType == 1)
{
txtChqNo.DataBindings.Add(new Binding("Text", bs, "vChqNo"));
txtChqDt.DataBindings.Add(new Binding("Text", bs, "vChqDt"));
txtDrAcc.DataBindings.Add(new Binding("Text", bs, "accCodeDR"));
txtCrAcc.DataBindings.Add(new Binding("Text", bs, "accCodeCR"));
txtPayName.DataBindings.Add(new Binding("Text", bs, "vPayName"));
txtAmount.DataBindings.Add(new Binding("Text", bs, "vAmount"));
}
txtRemarks.DataBindings.Add(new Binding("Text", bs, "vRemarks"));
txtVDt.Text = DateTime.Today.ToString();
if (vr.VoucherType == 1)
{
txtChqDt.Text = DateTime.Today.ToString();
}
if (txtvTypeID.Text == null || txtvTypeID.Text == string.Empty)
{
txtVType.Text = null;
}
else
{
txtVType.Text = dm.GetData("Select vType from vType Where vTypeID = '" + txtvTypeID.Text + "'").Rows[0]["vType"].ToString();
}
dgVDet.AutoGenerateColumns = false;
dgVDet.DataSource = bsDet;
dgVDet.Columns["vDetID"].DataPropertyName = "vDetID";
dgVDet.Columns["vID"].DataPropertyName = "vID";
dgVDet.Columns["accCode"].DataPropertyName = "accCode";
dgVDet.Columns["accName"].DataPropertyName = "accName";
dgVDet.Columns["accDebit"].DataPropertyName = "accDebit";
dgVDet.Columns["accCredit"].DataPropertyName = "accCredit";
dgVDet.Columns["accRemarks"].DataPropertyName = "accRemarks";
}
private void btnSave_Click(object sender, EventArgs e)
{
int rowCount = 0;
rowCount = dgVDet.Rows.Count - 1;
this.Validate();
bs.EndEdit();
double dCr = 0;
double dDr = 0;
Double.TryParse(txtTotDR.Text.ToString(), out dDr);
Double.TryParse(txtTotCR.Text.ToString(), out dCr);
if (!ds.HasChanges())
{
MessageBox.Show("No changes to save.", "Saving Records");
return;
}
if (dCr != dDr)
{
MessageBox.Show("Total of Debit and Credit are not equal.", "Saving Records");
return;
}
try
{
if (txtvTypeID.Text == null || txtvTypeID.Text == string.Empty)
{
return;
}
else
{
if (txtVNum.Text == null || txtVNum.Text == string.Empty)
{
int temp = int.Parse(this.txtvTypeID.Text.ToString());
ds.Tables[0].Rows[0]["vNo"] = vr.GetVoucherNum(temp);
}
}
vr.SaveVoucher(ds);
}
catch (MySqlException err)
{
MessageBox.Show(err.ErrorCode.ToString());
}
CellSum(5);
CellSum(6);
}
The problem: The vChqDt is the MySql DateTime field, and I am using DateTimePicker in C# Winform for user to select date. svChqDt is the parameter I used in SaveVoucher procedure, all fields are saved properly EXCEPT vChqDt....which is only getting NULL. I've search this forum for solution, and find that using ToString to change format of date to yyyy-mm-dd will do the trick. But the problem is where should I put it??
NOTE: Although txtChqDt is prefix with "txt" but it is actually DateTimePicker.
|
|
|
|
|
Try this:
txtChqDt.DataBindings.Add(new Binding("Value", bs, "vChqDt"));
(Bind to the "Value" property instead of the "Text" property of txtChqDt).
|
|
|
|
|
Thanks for your reply, I will definitely try this.. Meanwhile I've noticed that when form load, and if user manually select date in datetimepicker, ONLY then date is saved!!!! Otherwise not....
Any thoughts???
|
|
|
|
|
I'm not clear on what / when "Otherwise not" refers to.
All controls go through a series of events before they are fully initialized.
If in some cases you see properties that are not available (and then are), you may be accessing them too soon; or not the right property in the current context (e.g. "Text" versus "Value").
The easiest thing to do is simply display the properties of interest at key points in the program which will give one a better understanding of the "life" of a control.
|
|
|
|
|
Actually I am referring otherwise to when Form is load but user did not select any date in DateTimePicker and trying to saved the record with default Today's date.
|
|
|
|
|
Have you tried binding to "Value" yet?
|
|
|
|
|
Sorry for late reply, Yes I've tried the "Value"...
As you've noticed from my code that it's Master/Detail application, and I've create a relation VouchersToVoucherDetails in form code. Before modification of "Value" to DataBindings of vChqDt, vID was showing -1 on form load. But now it is showing null and when try to save the records it say vID cannot be null, and when press ok then no record is save to database.
|
|
|
|
|
Hi,
problem is solved, I've modified the "Text" and change to "Value", and also add fourth parameter "true" which enable formatting. After which all is going well.
Thanks for your guidance and support. Really appreciate.
Ahmed
|
|
|
|
|
Glad it's working for you now.
|
|
|
|
|
What is Constructor Overloading in C# .net ?
|
|
|
|
|
Google is your friend: Be nice and visit him often. He can answer questions a lot more quickly than posting them here...
A very quick search using your subject as the search term gave 198,000 hits: Google: "What is Constructor Overloading in C#"[^]
In future, please try to do at least basic research yourself, and not waste your time or ours.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
public class MyExceptionClass : Exception
{
public MyExceptionClass(string message, string extrainfo) : base(message)
{
}
}
Inherit from a class and call the base i think is the answer you are looking for
I want OO to have children that can speak to parent objects without having to wire them up but i guess i will have to wait for that bit of code canndy to come out one day.
|
|
|
|
|
Constructor overloading refers to designing a class that contains several constructors, each having a unique set of arguments for initializing class properties.
|
|
|
|
|
How to make Video cutter / merge that cut the videos and show start and end point in track bar like vcd Cutter player....
|
|
|
|
|
|
i have implemented sql dependency in windows service. when data will be changed in table then onchange event will fire and from there i am invoking a web service.
i will share my full code. i tested many time in my pc before installing the window service production pc. i works. suppose if i install in production pc today then it works for today but when i test next day then i was onchange event not firing.
so i found onchange event firing only first day and from the next day onchange event not firing. may be i made some mistake in code. so it is my request please some one see my code in details and help me where i made the mistake for which it is not working properly.
public partial class PartIndexer : ServiceBase
{
static string connectionString = "server=xxx;uid=xxx;password=xxx;database=xxx;Pooling=true;Connect Timeout=20;";
SqlDependency dep;
public PartIndexer()
{
InitializeComponent();
}
private string GetLoggedInUser()
{
string userName = "";
if (System.Security.Principal.WindowsIdentity.GetCurrent() != null)
{
userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
}
return userName;
}
#region OnStart
protected override void OnStart(string[] args)
{
BBALogger.Write("PartIndexer Service OnStart called start", BBALogger.MsgType.Info);
RegisterNotification();
MailSend();
BBALogger.Write("PartIndexer Service OnStart called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
}
#endregion
#region RegisterNotification
private void RegisterNotification()
{
string tmpdata = "";
BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
System.Data.SqlClient.SqlDependency.Start(connectionString);
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT TestTable FROM [dbo].ContentChangeLog";
dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(OnDataChange);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
dr.Read();
tmpdata = dr[0].ToString();
}
}
dr.Dispose();
cmd.Dispose();
}
}
catch (Exception ex)
{
BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
}
finally
{
BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);
}
}
#endregion
#region OnDataChange
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
((SqlDependency)sender).OnChange -= OnDataChange;
BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);
if (e.Source == SqlNotificationSource.Timeout)
{
MailSend();
BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Source != SqlNotificationSource.Data)
{
MailSend();
BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Type == SqlNotificationType.Change)
{
StartIndex();
BBALogger.Write("PartIndexer Service Data changed", BBALogger.MsgType.Info);
}
else
{
BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
}
RegisterNotification();
}
#endregion
#region StartIndex
void StartIndex()
{
}
#endregion
#region MailSend
void MailSend()
{
}
#endregion
#region OnStop
protected override void OnStop()
{
BBALogger.Write("PartIndexer Service StartIndex called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
MailNotify("STOPPED");
}
#endregion
}
another issue i noticed that when i start my windows service and leave it run in one day when i try to stop or restart the the windows service then i saw windows can not stop the service. it means definitely there is some flaw in my code which i am not being able to point out. so please help me. thanks
tbhattacharjee
|
|
|
|
|
So, how many times are you going to post this?
I would suggest you don't do it again unless you really want to be ignored.
When someone with experience sees your question, they'll answer.
|
|
|
|
|
hey guys 
trying to convert an access database to array to sort it by a calculated value.
the database connection is working frin in other functions
i keep getting the error message refering to the reader.GetString lines
An unhandled exception of type
'System.InvalidOperationException' occurred in System.Data.dll
Additional information: No data exists for the row/column.
can anyone help
static void displayinarray()
{
int rows = 0;
int averagewins;
try
{
OleDbCommand command = new OleDbCommand("select * from users", connection);
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read()) {rows++;};
string[,] results = new string[6,rows];
for (int loop = 0; loop <= rows; loop++)
{
results[0,loop] = reader.GetString(0);
results[1,loop] = reader.GetString(1);
results[2,loop] = reader.GetString(2);
results[3,loop] = reader.GetInt32(3).ToString();
results[4,loop] = reader.GetInt32(4).ToString();
averagewins = (reader.GetInt32(4) / reader.GetInt32(3))*100;
results[5,loop] = Convert.ToString(averagewins);
}
Console.Clear();
Console.WriteLine("{0}", rows);
Console.WriteLine(" ┌──────────────┬──────────────┬──────────────┬────────┬────────┬─────────┐");
Console.WriteLine(" │ username │ name │ surname │ played │ won │ % won │");
Console.WriteLine(" ├──────────────┼──────────────┼──────────────┼────────┼────────┼─────────┤");
for (int loop = 0; loop <= rows ; loop++)
{
Console.SetCursorPosition(2, loop + 4);
Console.WriteLine("│ {0} ", results[0,loop]);
Console.SetCursorPosition(17, loop + 4);
Console.WriteLine("│ {0} ", results[1,loop]);
Console.SetCursorPosition(32, loop + 4);
Console.WriteLine("│ {0} ", results[2,loop]);
Console.SetCursorPosition(47, loop + 4);
Console.WriteLine("│ {0} ", results[3,loop]);
Console.SetCursorPosition(56, loop + 4);
Console.WriteLine("│ {0} ", results[4,loop]);
Console.SetCursorPosition(65, loop + 4);
Console.WriteLine("│ {0} ", results[5,loop]);
Console.SetCursorPosition(75, loop + 4);
Console.WriteLine("│");
}
Console.WriteLine(" └──────────────┴──────────────┴──────────────┴────────┴────────┴─────────┘");
reader.Close();
connection.Close();
}
catch (OleDbException e)
{
Console.WriteLine("error: {0}", e.Errors[0].Message);
}
Console.ReadLine();
}}
modified 25-Apr-15 14:10pm.
|
|
|
|
|
A DataReader only allows for reading the result-set once. Where you count the lines with while (reader.Read()) {rows++;}; you're advancing the position of the DataReader to the end of the result-set and then it considers its job done, no further reading possible.
Instead of an array (for which you have to know the size in order to allocate it) just use a List (for which you don't have to know the amount of items beforehand). And I would suggest to create a class with the columns you're reading as properties, to use it as the items for the List.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
thanks for the quick reply
have tried removing the while (reader.Read()) {rows++;}; line and setting to read just a few lines by changing the rows by int rows = 3; as wasnt sure if it was interfering with reading the database.
was still getting the same error
prety new at c# & so was storing in an array as used them before
not used list yet & no idea how to create a class with columns as properties
using this still has the same error with not reading
static void displayinarray()
{
int rows = 3;
int averagewins;
try
{
OleDbCommand command = new OleDbCommand("select * from users", connection);
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
string[,] results = new string[6,rows];
for (int loop = 0; (reader.Read()); loop++)
{
results[0,loop] = reader.GetString(0);
results[1,loop] = reader.GetString(1);
results[2,loop] = reader.GetString(2);
results[3,loop] = reader.GetInt32(3).ToString();
results[4,loop] = reader.GetInt32(4).ToString();
averagewins = (reader.GetInt32(4) + reader.GetInt32(3))*100;
results[5,loop] = Convert.ToString(averagewins);
}
modified 25-Apr-15 14:40pm.
|
|
|
|
|
1) Don't do a select * , instead something like this: "select col1, col2, col3, col4, col5 from users"
That way you a) can be sure that you actually get 5 columns or a helpful exception and b) that you get the columns in the order you want them.
2) You're setting rows = 3 but you're reading all the returned records because rows is not part of the predicate of your for-loop.
3) Please check that under Visual Studio Menu: Debug -> Exceptions -> Common Langugage Runtime Exceptions the option Thrown is checked. Then, after applying the above modifications, run your program in debug mode, observe at which line an Exception is being thrown and please tell me.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
First of all, thanks. I've been coding for a while now and don't ask many question here but I do get a lot of answers here. I like to research my problems -- I learn more that way.
I have always used Visual Studio Pro for my C#, but lately I have been giving SharpDevolop a test drive. Does anybody here have any likes/dislikes about this software? Are there any major shortcomings?
Just wondering. Thanks.
|
|
|
|
|
Member 4654452 wrote: Are there any major shortcomings?
Yes, it is not Visual Studio.
It takes a while to get used to it, but it can run from USB, is rather fast, and doesn't take much resources. There's even a TFS-plugin, but that is still not enough to challenge Visual Studio
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
i like to know is it right approach to registering SQL Dependency again from onchange event ?
one guy review my code and told me registering everything again and again after each notification. As I can recall this will create a queue each time
so i comment this code in onchange event
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);
<pre>
if (e.Source == SqlNotificationSource.Timeout)
{
BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Source != SqlNotificationSource.Data)
{
BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Type == SqlNotificationType.Change)
{
BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
}
else
{
BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
}
CallWebService();
}</pre>
just see i comment this line //((SqlDependency)sender).OnChange -= OnDataChange; //RegisterNotification(); but after commenting i found onchange event is firing first time and it is not firing from second time.
guide me with right approach. here is my partial code
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);
if (e.Source == SqlNotificationSource.Timeout)
{
BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Source != SqlNotificationSource.Data)
{
BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Type == SqlNotificationType.Change)
{
BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
}
else
{
BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
}
CallWebService();
}
private void RegisterNotification()
{
string tmpdata = "";
BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
System.Data.SqlClient.SqlDependency.Start(connectionString);
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ActivityDate FROM [dbo].tablename";
dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(OnDataChange);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
dr.Read();
tmpdata = dr[0].ToString();
}
}
dr.Dispose();
cmd.Dispose();
}
}
catch (Exception ex)
{
BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
}
finally
{
BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);
}
}
thanks
tbhattacharjee
|
|
|
|
|
i have implemented sql dependency in windows service. when data will be changed in table then onchange event will fire and from there i am invoking a web service.
i will share my full code. i tested many time in my pc before installing the window service production pc. i works. suppose if i install in production pc today then it works for today but when i test next day then i was onchange event not firing.
so i found onchange event firing only first day and from the next day onchange event not firing. may be i made some mistake in code. so it is my request please some one see my code in details and help me where i made the mistake for which it is not working properly.
public partial class PartIndexer : ServiceBase
{
static string connectionString = "server=xxx;uid=xxx;password=xxx;database=xxx;Pooling=true;Connect Timeout=20;";
SqlDependency dep;
<pre>
public PartIndexer()
{
InitializeComponent();
}
private string GetLoggedInUser()
{
string userName = "";
if (System.Security.Principal.WindowsIdentity.GetCurrent() != null)
{
userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
}
return userName;
}
#region OnStart
protected override void OnStart(string[] args)
{
BBALogger.Write("PartIndexer Service OnStart called start", BBALogger.MsgType.Info);
RegisterNotification();
MailSend();
BBALogger.Write("PartIndexer Service OnStart called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
}
#endregion
#region RegisterNotification
private void RegisterNotification()
{
string tmpdata = "";
BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
System.Data.SqlClient.SqlDependency.Start(connectionString);
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT TestTable FROM [dbo].ContentChangeLog";
dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(OnDataChange);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
dr.Read();
tmpdata = dr[0].ToString();
}
}
dr.Dispose();
cmd.Dispose();
}
}
catch (Exception ex)
{
BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
}
finally
{
BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);
}
}
#endregion
#region OnDataChange
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
((SqlDependency)sender).OnChange -= OnDataChange;
BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);
if (e.Source == SqlNotificationSource.Timeout)
{
MailSend();
BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Source != SqlNotificationSource.Data)
{
MailSend();
BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Type == SqlNotificationType.Change)
{
StartIndex();
BBALogger.Write("PartIndexer Service Data changed", BBALogger.MsgType.Info);
}
else
{
BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
}
RegisterNotification();
}
#endregion
#region StartIndex
void StartIndex()
{
}
#endregion
#region MailSend
/// <param name="strStatus" />
void MailSend()
{
/* mail send code */
}
#endregion
#region OnStop
protected override void OnStop()
{
BBALogger.Write("PartIndexer Service StartIndex called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
MailNotify("STOPPED");
}
#endregion
}
another issue i noticed that when i start my windows service and leave it run in one day when i try to stop or restart the the windows service then i saw windows can not stop the service. it means definitely there is some flaw in my code which i am not being able to point out. so please help me.
another review my code and told me every time a new queue will be created at db side which may causes the problem. what is wrong in code for which every time a new queue will be created at db. thanks
thanks
tbhattacharjee
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
