Export Excel to SQL Databse Using Csharp
|
|
comments (0)
|
int row = 2;
string Path = @"f:\ACC_LEDGER_TROYEE.xls";
Excel.Application app = new Excel.Application();
Excel.Workbook workBook = app.Workbooks.Open(Path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
object colIndex1 = 1;
object colIndex2 = 2;
object colIndex3 = 3;
object colIndex4 = 4;
clsDatabaseConnectorForTroyee databaseConnectorForTroyee = new clsDatabaseConnectorForTroyee();
databaseConnectorForTroyee.mpubvConnectDatabaseForTroyee();
clsDatabaseConnectorForTroyee.gmpubstasqlconnSQLConnectionForTroyee.Open();
SqlCommand thisCommand = clsDatabaseConnectorForTroyee.gmpubstasqlconnSQLConnectionForTroyee.CreateCommand();
string lstrSQL = "SELECT * FROM ACC_LEDGER";
thisCommand.CommandText = lstrSQL;
SqlDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
workSheet.Cells[row, colIndex1].Value = Convert.ToString(thisReader["LEDGER_SERIAL"]);
workSheet.Cells[row, colIndex2].Value = Convert.ToString(thisReader["LEDGER_NAME"]);
workSheet.Cells[row, colIndex3].Value = Convert.ToString(thisReader["LEDGER_PARTY_TYPE"]);
row++;
}
thisReader.Close();
clsDatabaseConnectorForTroyee.gmpubstasqlconnSQLConnectionForTroyee.Close();
MessageBox.Show("Excel file created , you can find the file F:\\ACC_LEDGER_TROYEE.xls");
}
Export Excel to SQL Databse Using Csharp
|
|
comments (0)
|
int row = 2;
string Path = @"f:\ACC_LEDGER_TROYEE.xls";
Excel.Application app = new Excel.Application();
Excel.Workbook workBook = app.Workbooks.Open(Path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
object colIndex1 = 1;
object colIndex2 = 2;
object colIndex3 = 3;
object colIndex4 = 4;
clsDatabaseConnectorForTroyee databaseConnectorForTroyee = new clsDatabaseConnectorForTroyee();
databaseConnectorForTroyee.mpubvConnectDatabaseForTroyee();
clsDatabaseConnectorForTroyee.gmpubstasqlconnSQLConnectionForTroyee.Open();
SqlCommand thisCommand = clsDatabaseConnectorForTroyee.gmpubstasqlconnSQLConnectionForTroyee.CreateCommand();
string lstrSQL = "SELECT * FROM ACC_LEDGER";
thisCommand.CommandText = lstrSQL;
SqlDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
workSheet.Cells[row, colIndex1].Value = Convert.ToString(thisReader["LEDGER_SERIAL"]);
workSheet.Cells[row, colIndex2].Value = Convert.ToString(thisReader["LEDGER_NAME"]);
workSheet.Cells[row, colIndex3].Value = Convert.ToString(thisReader["LEDGER_PARTY_TYPE"]);
row++;
}
thisReader.Close();
clsDatabaseConnectorForTroyee.gmpubstasqlconnSQLConnectionForTroyee.Close();
MessageBox.Show("Excel file created , you can find the file F:\\ACC_LEDGER_TROYEE.xls");
}
Inserting And Retrieve Image
|
|
comments (0)
|
private void mpriiInsertIntoImage()
{
// int liReturnValue = -1;
try
{
if (DatabaseConnector.gmpubstasqlconnSQLConnection.State == ConnectionState.Open)
{
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
}
DatabaseConnector.gmpubstasqlconnSQLConnection.Open();
SqlCommand lsqlcommSQLCommand = DatabaseConnector.gmpubstasqlconnSQLConnection.CreateCommand();
byte[] lbyteEmployeePhoto = ReadFile(txtBrowse.Text);
mpristrSQLQuery = "insert into SAM_CUSTOMER_IMAGE(FORM_NUMBER, CUSTOMER_IMAGE) values ('" + txtFormNo.Text + "', @ImageData)";
SqlCommand SqlCom = new SqlCommand(mpristrSQLQuery, DatabaseConnector.gmpubstasqlconnSQLConnection);
SqlCom.Parameters.Add(new SqlParameter("@ImageData", (object)lbyteEmployeePhoto));
SqlCom.ExecuteNonQuery();
mpristrSQLQuery = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//return liReturnValue;
}
}
byte[] ReadFile(string sPath)
{
byte[] data = null;
FileInfo fInfo = new FileInfo(sPath);
long numBytes = fInfo.Length;
FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fStream);
data = br.ReadBytes((int)numBytes);
return data;
}
if (mpribIsExists())
{
pb1Photo.Image = null;
pb2Photo.Image = null;
pb3Photo.Image = null;
pb4Photo.Image = null;
pb5Photo.Image = null;
int i = 0;
Byte[] byteBLOBData1 = new Byte[0];
Byte[] byteBLOBData2 = new Byte[0];
Byte[] byteBLOBData3 = new Byte[0];
Byte[] byteBLOBData4 = new Byte[0];
Byte[] byteBLOBData5 = new Byte[0];
string lstrSQL = "SELECT LAND_CONFIRM_PI_DOLIL_ATTACMENT FROM LAND_CONFIRM_PI_DOLIL_REG WHERE LAND_CONFIRM_PI_DOLIL_REG_NO='" + txtDoliRegNo.Text.Trim() + "'";
DatabaseConnector.gmpubstasqlconnSQLConnection.Open();
SqlCommand thisCommand = DatabaseConnector.gmpubstasqlconnSQLConnection.CreateCommand();
thisCommand.CommandText = lstrSQL;
SqlDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
if (i == 0)
{
byteBLOBData1 = (Byte[])(thisReader["LAND_CONFIRM_PI_DOLIL_ATTACMENT"]);
MemoryStream ms1 = new MemoryStream(byteBLOBData1);
pb1Photo.Image = Image.FromStream(ms1);
}
if (i == 1)
{
byteBLOBData2 = (Byte[])(thisReader["LAND_CONFIRM_PI_DOLIL_ATTACMENT"]);
MemoryStream ms2 = new MemoryStream(byteBLOBData2);
pb2Photo.Image = Image.FromStream(ms2);
}
if (i == 2)
{
byteBLOBData3 = (Byte[])(thisReader["LAND_CONFIRM_PI_DOLIL_ATTACMENT"]);
MemoryStream ms3 = new MemoryStream(byteBLOBData3);
pb3Photo.Image = Image.FromStream(ms3);
}
if (i == 3)
{
byteBLOBData4 = (Byte[])(thisReader["LAND_CONFIRM_PI_DOLIL_ATTACMENT"]);
MemoryStream ms4 = new MemoryStream(byteBLOBData4);
pb4Photo.Image = Image.FromStream(ms4);
}
if (i == 4)
{
byteBLOBData5 = (Byte[])(thisReader["LAND_CONFIRM_PI_DOLIL_ATTACMENT"]);
MemoryStream ms5 = new MemoryStream(byteBLOBData5);
pb5Photo.Image = Image.FromStream(ms5);
}
i++;
}
private bool mpribIsExists()
{
try
{
OperationGateway operationGateway = new OperationGateway();
return operationGateway.mpubiIsExistsRecord("LAND_CONFIRM_PI_DOLIL_REG", "LAND_CONFIRM_PI_DOLIL_REG_NO", txtDoliRegNo.Text.Trim());
}
catch (Exception pexpIsExistsException)
{
MessageBox.Show(pexpIsExistsException.Message);
return false;
}
}
Main Program
|
|
comments (0)
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Data;
using System.IO;
using net.ERP.Constants;
using net.ERP.Utility;
using net.ERP.DatabaseSchema.TableSchema;
using net.ERP.DatabaseSchema.DBExistanceChecking;
using net.ERP.DatabaseSchema.DefaultDataConfiguration;
namespace net.ERP.MainEntryPoint
{
static class MainProgram
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
try
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
try
{
//Create an instance of StreamReader to read from a file and the using statement also closes the StreamReader.
using (StreamReader sr = new StreamReader("Server.txt"))
{
string lstrFileText = null;
//Read and display lines from the file until the end of the file is reached.
while ((lstrFileText = sr.ReadLine()) != null)
{
CommonConstants.gmpubstastrServerName = lstrFileText;
}
}
}
catch (Exception pexpServerSearch)
{
//Let the user know what went wrong.
MessageBox.Show(pexpServerSearch.Message);
}
DatabaseConnector databaseConnector = new DatabaseConnector();
databaseConnector.mpubvConnectDatabase(CommonConstants.gmpubstastrServerName);
if (DatabaseConnector.gmpubstasqlconnSQLConnection != null)
{
DatabaseExistanceChecking databaseExistanceChecking = new DatabaseExistanceChecking();
if (databaseExistanceChecking.mpubbIsDatabaseExists(CommonConstants.gmpubstastrServerName) == false)
{
Application.Run(new frmNewCompanyInstallation());
MessageBox.Show("Database created successfully. 'Software Support Team' suggests you to close the application and continue from the second time for processing.", CommonConstants.gmpubcstrUserConfirmation, MessageBoxButtons.OK, MessageBoxIcon.Information);
Application.Run(new mdiNetERP());
}
else
{
Application.Run(new mdiNetERP());
}
}
}
catch (Exception ae)
{
MessageBox.Show(ae .Message .ToString ());
}
finally
{
if (DatabaseConnector.gmpubstasqlconnSQLConnection.State == ConnectionState.Open)
{
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
}
}
}
}
}
Database Existence Checking
|
|
comments (0)
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using net.ERP.Constants;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace net.ERP.DatabaseSchema.DBExistanceChecking
{
class DatabaseExistanceChecking:CommonConstants
{
public bool mpubbIsDatabaseExists(string pstrServerName)
{
SqlConnection lprisqlconnSQLConnection = new SqlConnection("Server=" + pstrServerName + ";Integrated security=SSPI;database=master");
try
{
lprisqlconnSQLConnection.Open();
SqlCommand lsqlcommSQLCommand = lprisqlconnSQLConnection.CreateCommand();
lsqlcommSQLCommand.CommandText = "SELECT COUNT(*) AS TOTAL_RECORDS FROM master.dbo.sysdatabases WHERE NAME = 'NET_ERP_REMS'";
SqlDataReader lsqldrSqlDataReader = lsqlcommSQLCommand.ExecuteReader();
if (lsqldrSqlDataReader.Read())
{
int liTotalDatabases = (int)lsqldrSqlDataReader["TOTAL_RECORDS"];
lsqldrSqlDataReader.Close();
if (liTotalDatabases > 0)
{
return true;
}
else
{
return false;
}
}
return true;
}
catch (Exception pexpDBExistance)
{
MessageBox.Show(pexpDBExistance.ToString(), gmpubcstrUserConfirmation, MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
finally
{
if (lprisqlconnSQLConnection.State == ConnectionState.Open)
{
lprisqlconnSQLConnection.Close();
}
}
}
}
}
Blank Database Creation
|
|
comments (0)
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
using net.ERP.Constants;
namespace net.ERP.DatabaseSchema.DatabaseCreationForServer
{
class BlankDatabaseCreation:CommonConstants
{
public void mpubvCreateDatabase(string pstrServerName)
{
SqlConnection lprisqlconnSQLConnection = new SqlConnection("Server=" + pstrServerName + ";Integrated security=SSPI;database=master");
try
{
lprisqlconnSQLConnection.Open();
SqlCommand lsqlcommSqlCommand = new SqlCommand("CREATE DATABASE " + gmpubcstrDatabaseName, lprisqlconnSQLConnection);
lsqlcommSqlCommand.ExecuteNonQuery();
}
catch (Exception pexpDBCreation)
{
MessageBox.Show(pexpDBCreation.ToString(), CommonConstants.gmpubcstrUserConfirmation, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
if (lprisqlconnSQLConnection.State == ConnectionState.Open)
{
lprisqlconnSQLConnection.Close();
}
}
}
}
}
Constant Declaration
|
|
comments (0)
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace net.ERP.Constants
{
public class CommonConstants
{
public static string gmpubstastrServerName = null;
//public const string gmpubcstrServerName = "MAHBUB-PC";
public const string gmpubcstrDatabaseName = "NET_ERP_REMS";
public const string gmpubcstrUserName = "sa";
public const string gmpubcstrPassword = "";
//public const string gmpubcstrPassword = "manager";
public const string gmpubcstrMaxNumOfAllowedRecords = "No more record is allowed to add.";
public const string gmpubcstrUserConfirmation = "net.ERP User Confirmation";
public const string gmpubcstrDuplicationCheckingOfRecord = "This 'Inquiry' already exists in the database.";
public const string gmpubcstrRetryMsg = "Please try again to continue.";
public const string gmpubcstrRelatedRecordInUseNotification = "Related record exists!";
public const string gmpubcstrInsertOperationHeading = "Insert Operation Confirmation";
public const string gmpubcstrInsertOperationQuestion = "Are you sure you want to insert this 'Inquiry' details ?";
public const string gmpubcstrInsertOperationConfirmation = "Insert process completed successfully.";
public const string gmpubcstrInsertOperationFailureNotification = "Failed to insert data in the database. Please try again to continue.";
public const string gmpubcstrEditOperationHeading = "Edit Operation Confirmation";
public const string gmpubcstrEditOperationQuestion = "Are you sure you want to edit this selected 'Inquiry' details ?";
public const string gmpubcstrEditOperationConfirmation = "Edit process completed successfully.";
public const string gmpubcstrEditOperationFailureNotification = "Failed to edit data in the database. Please try again to continue.";
public const string gmpubcstrValidityCheckingBeforeEdit = "Invalid key to edit! Please try again to continue.";
public const string gmpubcstrDeleteOperationHeading = "Delete Operation Confirmation";
public const string gmpubcstrDeleteOperationQuestion = "Are you sure you want to delete this selected 'Inquiry' details ?";
public const string gmpubcstrDeleteOperationConfirmation = "Delete process completed successfully.";
public const string gmpubcstrDeleteOperationFailureNotification = "Failed to delete data from the database. Please try again to continue.";
public const string gmpubcstrValidityCheckingBeforeDelete = "Invalid key to delete! Please try again to continue.";
public const string gmpubcstrExistanceOfRelatedRecordWhenDelete = "Related record exists! Please try again to continue.";
public const string gmpubcstrDataErrorMessage = "Invalid input for this field!";
public const string gmpubcstrDataErrorHeading = "Data Validation Checking";
public const string gmpubcstrAddModeCaption = "Add Mode";
public const string gmpubcstrEditModeCaption = "Edit Mode";
public const string gmpubcstrDeleteModeCaption = "Delete Mode";
public const string gmpubcstrDisplayModeCaption = "Display Mode";
public const string gmpubcstrEndofList = "End of List";
public enum ACTION_MODE
{
ADD_MODE = 0,
EDIT_MODE = 1
};
}
}
Database Connector
|
|
comments (0)
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using net.ERP.Constants;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace net.ERP.Utility
{
class DatabaseConnector:CommonConstants
{
private string mpristrDBConnectionString = null;
public static SqlConnection gmpubstasqlconnSQLConnection = null;
public static SqlConnection gmpubstasqlconnSQLConnectionTemp = null;
public void mpubvConnectDatabase(string pstrServerName)
{
try
{
mpristrDBConnectionString = "Data Source=" + pstrServerName + ";Initial Catalog=" + gmpubcstrDatabaseName + ";User ID=" + gmpubcstrUserName + ";Password=" + gmpubcstrPassword;
gmpubstasqlconnSQLConnection = new SqlConnection(mpristrDBConnectionString);
gmpubstasqlconnSQLConnectionTemp = new SqlConnection(mpristrDBConnectionString);
}
catch (Exception pexpDBConnection)
{
MessageBox.Show(pexpDBConnection.Message);
}
finally
{
mpristrDBConnectionString = null;
}
}
}
}
DateTime Picker in GridView
|
|
comments (0)
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace net.ERP.Modules.Land.GUI.Transactions
{
public class DateTimePickerColumn : DataGridViewColumn
{
public DateTimePickerColumn()
: base(new DateTimePickerCell())
{
}
public override DataGridViewCell CellTemplate
{
get
{
return base.CellTemplate;
}
set
{
// Ensure that the cell used for the template is a DateTimePickerCell.
if (value != null &&
!value.GetType().IsAssignableFrom(typeof(DateTimePickerCell)))
{
throw new InvalidCastException("Must be a DateTimePickerCell");
}
base.CellTemplate = value;
}
}
private void InitializeComponent()
{
System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle1 = new System.Windows.Forms.DataGridViewCellStyle();
//
// DateTimePickerColumn
//
dataGridViewCellStyle1.Format = "d";
dataGridViewCellStyle1.NullValue = null;
this.DefaultCellStyle = dataGridViewCellStyle1;
}
}
public class DateTimePickerCell : DataGridViewTextBoxCell
{
public DateTimePickerCell()
: base()
{
// Use the custom defined date format.
this.Style.Format = DateTime.Today.ToString("MM-dd-yyyy");
}
public override void InitializeEditingControl(int rowIndex, object
initialFormattedValue, DataGridViewCellStyle dataGridViewCellStyle)
{
// Set the value of the editing control to the current cell value.
base.InitializeEditingControl(rowIndex, initialFormattedValue,
dataGridViewCellStyle);
DateTimePickerEditingControl ctl = (DateTimePickerEditingControl)DataGridView.EditingControl;
DateTime d;
ctl.Value = DateTime.TryParse((Value ?? "").ToString(), out d) ? d : DateTime.Now;
/*Check whether the datagridview is databound/unbound. In both cases if the value of the cell
isn't null then check the DateTimePickerEditing Control else uncheck it.*/
if (ctl.EditingControlDataGridView.CurrentCell.OwningColumn.IsDataBound)
ctl.Checked = Value.ToString() == "" ? false : Value == null ? false : true;
else
ctl.Checked = Value == null ? false : true;
}
public override Type EditType
{
get
{
// Return the type of the editing contol that DateTimePickerCell uses.
return typeof(DateTimePickerEditingControl);
}
}
public override Type ValueType
{
get
{
// Return the type of the value that DateTimePickerCell contains.
return typeof(DateTime);
}
}
public override object DefaultNewRowValue
{
get
{
//Return null as the default value for new row.
return null;
}
}
}
class DateTimePickerEditingControl : DateTimePicker, IDataGridViewEditingControl
{
DataGridView dataGridView;
private bool valueChanged = false;
int rowIndex;
public DateTimePickerEditingControl()
{
this.Format = DateTimePickerFormat.Custom;
// this.CustomFormat = DateTime.Now.ToString("MM-dd-yyyy");
//this.ShowCheckBox = true;
//this.Checked = false;
}
// Implements the IDataGridViewEditingControl.EditingControlFormattedValue
// property.
public object EditingControlFormattedValue
{
get
{
return this.Value.ToString();
}
set
{
if (value is String)
{
this.Value = DateTime.Parse((String)value);
//this.Value = Convert.ToString(value);
}
}
}
// Implements the
// IDataGridViewEditingControl.GetEditingControlFormattedValue method.
public object GetEditingControlFormattedValue(
DataGridViewDataErrorContexts context)
{
return EditingControlFormattedValue;
}
// Implements the
// IDataGridViewEditingControl.ApplyCellStyleToEditingControl method.
public void ApplyCellStyleToEditingControl(
DataGridViewCellStyle dataGridViewCellStyle)
{
this.Font = dataGridViewCellStyle.Font;
this.CalendarForeColor = dataGridViewCellStyle.ForeColor;
this.CalendarMonthBackground = dataGridViewCellStyle.BackColor;
}
// Implements the IDataGridViewEditingControl.EditingControlRowIndex
// property.
public int EditingControlRowIndex
{
get
{
return rowIndex;
}
set
{
rowIndex = value;
}
}
// Implements the IDataGridViewEditingControl.EditingControlWantsInputKey
// method.
public bool EditingControlWantsInputKey(
Keys key, bool dataGridViewWantsInputKey)
{
// Let the DateTimePicker handle the keys listed.
switch (key & Keys.KeyCode)
{
case Keys.Left:
case Keys.Up:
case Keys.Down:
case Keys.Right:
case Keys.Home:
case Keys.End:
case Keys.PageDown:
case Keys.PageUp:
return true;
default:
return !dataGridViewWantsInputKey;
}
}
// Implements the IDataGridViewEditingControl.PrepareEditingControlForEdit
// method.
public void PrepareEditingControlForEdit(bool selectAll)
{
// No preparation needs to be done.
}
// Implements the IDataGridViewEditingControl
// .RepositionEditingControlOnValueChange property.
public bool RepositionEditingControlOnValueChange
{
get
{
return false;
}
}
// Implements the IDataGridViewEditingControl
// .EditingControlDataGridView property.
public DataGridView EditingControlDataGridView
{
get
{
return dataGridView;
}
set
{
dataGridView = value;
}
}
// Implements the IDataGridViewEditingControl
// .EditingControlValueChanged property.
public bool EditingControlValueChanged
{
get
{
return valueChanged;
}
set
{
valueChanged = value;
}
}
// Implements the IDataGridViewEditingControl
// .EditingPanelCursor property.
public Cursor EditingPanelCursor
{
get
{
return base.Cursor;
}
}
protected override void OnValueChanged(EventArgs eventargs)
{
// Notify the DataGridView that the contents of the cell
// have changed.
valueChanged = true;
this.EditingControlDataGridView.NotifyCurrentCellDirty(true);
base.OnValueChanged(eventargs);
}
}
}
Operation Gateway
|
|
comments (0)
|
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
using net.ERP.Utility;
namespace net.ERP.Utility
{
public class OperationGateway
{
private ArrayList mprialDependentTableNameList;
public static int mpubiExecuteSQLStmt(string pstrSQL)
{
int liTotalEffectedRecords = 0;
try
{
SqlCommand mprisqlcommSqlCommand = null;
if (DatabaseConnector.gmpubstasqlconnSQLConnection.State == ConnectionState.Open)
{
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
}
DatabaseConnector.gmpubstasqlconnSQLConnection.Open();
mprisqlcommSqlCommand = new SqlCommand(pstrSQL, DatabaseConnector.gmpubstasqlconnSQLConnection);
liTotalEffectedRecords = mprisqlcommSqlCommand.ExecuteNonQuery();
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
return liTotalEffectedRecords;
}
catch (SqlException psqlexpStmtExecution)
{
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
MessageBox.Show(psqlexpStmtExecution.Message.ToString());
return liTotalEffectedRecords;
}
}
public static bool mpubstabPrimaryKeyValueNotUsedAsForeignKeyValue(ArrayList larrLstlDependentTableNameList, ArrayList larrLstlInquiryColumnName, string lstrInquiryColumnValue)
{
bool PrimaryKeyValueNotUsedAsForeignKeyValue = true;
try
{
if (DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.State == ConnectionState.Open)
{
DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();
}
for (int i = 0; i < larrLstlDependentTableNameList.Count; i++)
{
DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Open();
SqlCommand lsqlcommSQLCommand = DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.CreateCommand();
lsqlcommSQLCommand.CommandText = "SELECT " + larrLstlInquiryColumnName[i] + " FROM " + larrLstlDependentTableNameList[i] + " WHERE " + larrLstlInquiryColumnName[i] + " = '" + lstrInquiryColumnValue + "'";
SqlDataReader lsqldrSqlDataReader = lsqlcommSQLCommand.ExecuteReader();
while (lsqldrSqlDataReader.Read())
{
PrimaryKeyValueNotUsedAsForeignKeyValue = false;
break;
}
lsqldrSqlDataReader.Close();
DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();
}
return PrimaryKeyValueNotUsedAsForeignKeyValue;
}
catch (SqlException psqlexpRecordExistance)
{
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
MessageBox.Show(psqlexpRecordExistance.Message.ToString());
return PrimaryKeyValueNotUsedAsForeignKeyValue;
}
finally
{
DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();
}
}
public bool mpubiIsExistsRecord(string pstrTableName, string pstrColumnName, string pstrCheckingValue)
{
try
{
int liReturnValue = -1;
if (DatabaseConnector.gmpubstasqlconnSQLConnection.State == ConnectionState.Open)
{
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
}
DatabaseConnector.gmpubstasqlconnSQLConnection.Open();
SqlCommand lsqlcommSQLCommand = DatabaseConnector.gmpubstasqlconnSQLConnection.CreateCommand();
lsqlcommSQLCommand.CommandText = "SELECT " + pstrColumnName + " FROM " + pstrTableName + " WHERE " + pstrColumnName + " = '" + pstrCheckingValue + "'";
SqlDataReader lsqldrSqlDataReader = lsqlcommSQLCommand.ExecuteReader();
while (lsqldrSqlDataReader.Read())
{
liReturnValue = 1;
}
lsqldrSqlDataReader.Close();
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
if (liReturnValue == -1)
{
return false;
}
else
{
return true;
}
}
catch (SqlException psqlexpRecordExistance)
{
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
MessageBox.Show(psqlexpRecordExistance.Message.ToString());
return false;
}
}
//ADDED BY BINDESHWAR
//METHOD PURPOSE: RETURNS A REQUIRED COLUMN VALUE IN A RECORD BY SELECTING IT USING WHERE CLAUSE WITH ANOTHER INQUIRY COLUMN VALUE IN THE SAME RECORD.
public static string mpristrGetOneColumnValueByAnotherColumnValue(string lstrTableName, string lstrRequiredColumnName, string lstrInquiryColumnName, string lstrInquiryColumnValue)
{
string lstrReturnRequiredColumnValue = "";
try
{
if (DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.State == ConnectionState.Open)
{
DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();
}
DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Open();
SqlCommand lsqlcommSQLCommand = DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.CreateCommand();
lsqlcommSQLCommand.CommandText = "SELECT " + lstrRequiredColumnName + " FROM " + lstrTableName + " WHERE " + lstrInquiryColumnName + " = '" + lstrInquiryColumnValue + "'";
SqlDataReader lsqldrSqlDataReader = lsqlcommSQLCommand.ExecuteReader();
while (lsqldrSqlDataReader.Read())
{
//CODE MODIFIED BY BINDESHWAR
lstrReturnRequiredColumnValue = Convert.ToString(lsqldrSqlDataReader[lstrRequiredColumnName]);
//CODE MODIFIED BY BINDESHWAR
}
lsqldrSqlDataReader.Close();
DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();
return lstrReturnRequiredColumnValue;
}
catch (SqlException psqlexpRecordExistance)
{
DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();
MessageBox.Show(psqlexpRecordExistance.Message.ToString());
return lstrReturnRequiredColumnValue;
}
//finally
//{
// DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
//}
}
//METHOD PURPOSE: RETURNS A REQUIRED COLUMN VALUE IN A RECORD BY SELECTING IT USING WHERE CLAUSE WITH ANOTHER TWO INQUIRY COLUMN VALUE IN THE SAME RECORD.
public static string mpubstastrGetOneColumnValueByTwoColumnValues(string lstrTableName, string lstrRequiredColumnName, string lstrInquiryColumnNameOne, string lstrInquiryColumnValueOne, string lstrInquiryColumnNameTwo, string lstrInquiryColumnValueTwo)
{
string lstrReturnRequiredColumnValue = "";
try
{
if (DatabaseConnector.gmpubstasqlconnSQLConnection.State == ConnectionState.Open)
{
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
}
DatabaseConnector.gmpubstasqlconnSQLConnection.Open();
SqlCommand lsqlcommSQLCommand = DatabaseConnector.gmpubstasqlconnSQLConnection.CreateCommand();
lsqlcommSQLCommand.CommandText = "SELECT " + lstrRequiredColumnName + " FROM " + lstrTableName + " WHERE " + lstrInquiryColumnNameOne + " = '" + lstrInquiryColumnValueOne + "' AND " + lstrInquiryColumnNameTwo + " = '" + lstrInquiryColumnValueTwo + "'";
SqlDataReader lsqldrSqlDataReader = lsqlcommSQLCommand.ExecuteReader();
while (lsqldrSqlDataReader.Read())
{
//CODE MODIFIED BY BINDESHWAR
lstrReturnRequiredColumnValue = Convert.ToString(lsqldrSqlDataReader[lstrRequiredColumnName]);
//CODE MODIFIED BY BINDESHWAR
}
lsqldrSqlDataReader.Close();
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
return lstrReturnRequiredColumnValue;
}
catch (SqlException psqlexpRecordExistance)
{
DatabaseConnector.gmpubstasqlconnSQLConnection.Close();
MessageBox.Show(psqlexpRecordExistance.Message.ToString());
return lstrReturnRequiredColumnValue;
}
}
private void mprivInitDependentTableNameList()
{
mprialDependentTableNameList = new ArrayList();
mprialDependentTableNameList.Clear();
mprialDependentTableNameList.Add("LAND_REGISTRATION");
}
}
}