public string GetMaxIdWithPrfix(string coloumName, string rightStringLength, string initialValue, string tableName, string prefix)
{
string id = "";
string maxId = "";
_selectQuery = "SELECT ISNULL(MAX(RIGHT(" + coloumName + ", " + rightStringLength + ")) + 1, " + initialValue + ") AS maxID " +
" FROM " + tableName + " where left(" + coloumName + "," + prefix.Length + ")='" + prefix + "' ";
SQLDAL dal = new SQLDAL();
DataTable dt = dal.Select(_selectQuery).Data;
if (dt != null && dt.Rows.Count > 0)
{
id = (dt.Rows[0]["maxID"].ToString());
}
if (id.Length < initialValue.Length)
{
int prefixZero = initialValue.Length - id.Length;
string _prefix = "";
for (int i = 0; i < prefixZero; i++)
{
_prefix += "0";
}
id = _prefix + id;
}
maxId = prefix + id;
return(maxId);
}
public ActionResult DeviceBulkUpload(HttpPostedFileBase fileUpload)
{
string UploadPath = Server.MapPath(ConfigurationManager.AppSettings["DevicePath"].ToString());
string FilePath = string.Empty;
DataSet dataSet = new DataSet();
SQLDAL sQLDAL = new SQLDAL();
string SQLConStr = ConfigurationManager.ConnectionStrings["MSSQL_ConnectionString"].ConnectionString;
if (fileUpload != null)
{
if (!Directory.Exists(UploadPath))
{
Directory.CreateDirectory(UploadPath);
}
FilePath = UploadPath + Path.GetFileName(fileUpload.FileName);
string extension = Path.GetExtension(fileUpload.FileName);
fileUpload.SaveAs(FilePath);
string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + FilePath + "';Extended Properties='Excel 8.0;HDR=YES'";
string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
DataTable mdttable = new DataTable();
switch (extension)
{
case ".xls":
conString = string.Format(conString, FilePath);
mdttable = ForXLSExcelFile(conString);
break;
case ".xlsx":
conString = string.Format(constr, FilePath);
mdttable = ForXLSXExcelFile(conString);
break;
case ".csv":
mdttable = ForCSVFile(FilePath);
break;
}
SqlParameter[] sqlParameterCSV = new SqlParameter[3];
sqlParameterCSV[0] = new SqlParameter("@tbl_UploadDevice", mdttable);
sqlParameterCSV[1] = new SqlParameter("@mint_clientId", Convert.ToInt32(Request.Form["ClientId"]));
sqlParameterCSV[2] = new SqlParameter("@EnteredBy", Convert.ToInt32(Session["UserId"]));
dataSet = sQLDAL.ExecuteDataset(SQLConStr, "proc_InsertDeviceDetails_set", sqlParameterCSV);
if (dataSet.Tables.Count > 1)
{
if (dataSet.Tables[1].Rows.Count > 0)
{
ExportToExcel(dataSet.Tables[1], extension);
//string path = "<script>window.location.href='ExportToExcel(" + dataSet.Tables[1] + "," + extension + ")';</script>";
//ViewBag.DownloadRejectFile = "";
}
}
}
return(Json(""));
}
public DataTable GetAllEvents()
{
DataTable dtVendorInfo = new DataTable();
SQLDAL objSql = new SQLDAL();
MySqlCommand objCmd = new MySqlCommand();
MySqlDataAdapter objda = new MySqlDataAdapter();
try
{
objCmd = objSql.CreateCommand(objCmd, CommandType.StoredProcedure, "View");
objSql.AddParameter(objCmd, "pAction", MySqlDbType.VarChar, ParameterDirection.Input, 20, "GetEvent");
objda.SelectCommand = objCmd;
objda.Fill(dtVendorInfo);
}
catch (Exception ex)
{
}
finally
{
if (!(objCmd == null))
{
objCmd.Dispose();
if (objSql != null)
{
objSql.Dispose();
}
if (objda != null)
{
objda.Dispose();
}
}
}
return(dtVendorInfo);
}
public string CreateEvent(int intEventId, int intUserId, string strEventName, string strStartDate, string strStartTime, string strEndDate, string strEndTime,
string strDescription, string strEventType, string strVenueEventType, string strAddress, string strVenue, string strCity,
string strState, string strCountry, string strPinCode, string strEventLogo, string strCoverPicture, string strFinalEventType, string strDraft, string strStatus, int intCreatedById)
{
string strRetVal = "";
SQLDAL objSql = new SQLDAL();
MySqlCommand objCmd = new MySqlCommand();
MySqlDataAdapter objda = new MySqlDataAdapter();
try
{
objCmd = objSql.CreateCommand(objCmd, CommandType.StoredProcedure, "EventsSp");
objSql.AddParameter(objCmd, "pAction", MySqlDbType.VarChar, ParameterDirection.Input, 50, "InsertEvent");
objSql.AddParameter(objCmd, "peventId", MySqlDbType.Int32, ParameterDirection.Input, 50, intEventId);
objSql.AddParameter(objCmd, "puserId", MySqlDbType.Int32, ParameterDirection.Input, 50, intUserId);
objSql.AddParameter(objCmd, "peventName", MySqlDbType.VarChar, ParameterDirection.Input, 50, strEventName);
objSql.AddParameter(objCmd, "pstartDate", MySqlDbType.VarChar, ParameterDirection.Input, 50, strStartDate);
objSql.AddParameter(objCmd, "pstartTime", MySqlDbType.VarChar, ParameterDirection.Input, 50, strStartTime);
objSql.AddParameter(objCmd, "pendDate", MySqlDbType.VarChar, ParameterDirection.Input, 50, strEndDate);
objSql.AddParameter(objCmd, "pendTime", MySqlDbType.VarChar, ParameterDirection.Input, 50, strEndTime);
objSql.AddParameter(objCmd, "pdescription", MySqlDbType.VarChar, ParameterDirection.Input, 50, strDescription);
objSql.AddParameter(objCmd, "peventType", MySqlDbType.VarChar, ParameterDirection.Input, 50, strEventType);
objSql.AddParameter(objCmd, "pvenueEventType", MySqlDbType.VarChar, ParameterDirection.Input, 50, strVenueEventType);
objSql.AddParameter(objCmd, "paddress", MySqlDbType.VarChar, ParameterDirection.Input, 50, strAddress);
objSql.AddParameter(objCmd, "pvenue", MySqlDbType.VarChar, ParameterDirection.Input, 50, strVenue);
objSql.AddParameter(objCmd, "pstate", MySqlDbType.VarChar, ParameterDirection.Input, 50, strCity);
objSql.AddParameter(objCmd, "pcity", MySqlDbType.VarChar, ParameterDirection.Input, 50, strState);
objSql.AddParameter(objCmd, "pcountry", MySqlDbType.VarChar, ParameterDirection.Input, 50, strCountry);
objSql.AddParameter(objCmd, "ppinCode", MySqlDbType.VarChar, ParameterDirection.Input, 50, strPinCode);
objSql.AddParameter(objCmd, "peventLogo", MySqlDbType.VarChar, ParameterDirection.Input, 50, strEventLogo);
objSql.AddParameter(objCmd, "pcoverPic", MySqlDbType.VarChar, ParameterDirection.Input, 50, strCoverPicture);
objSql.AddParameter(objCmd, "pfinaleventtype", MySqlDbType.VarChar, ParameterDirection.Input, 50, strFinalEventType);
objSql.AddParameter(objCmd, "pdraft", MySqlDbType.VarChar, ParameterDirection.Input, 50, strDraft);
objSql.AddParameter(objCmd, "pstatus", MySqlDbType.VarChar, ParameterDirection.Input, 50, strStatus);
objSql.AddParameter(objCmd, "pCreatedBy", MySqlDbType.Int32, ParameterDirection.Input, 25, intCreatedById);
strRetVal = objCmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
throw ex;
//CreateErrorlog.WriteError(ex.Message.ToString() + "\r\n" + ex.StackTrace);
}
finally
{
if (!(objCmd == null))
{
objCmd.Dispose();
if (objSql != null)
{
objSql.Dispose();
}
if (objda != null)
{
objda.Dispose();
}
}
}
return(strRetVal);
}
public DocumentField.SendDocumentInfo BuildDocFields()
{
SQLDAL dal = new SQLDAL();
List <DocumentField.SignerInfo> signerdetails = new List <DocumentField.SignerInfo>();
DocumentField.SendDocumentInfo docf = new DocumentField.SendDocumentInfo();
docf.DocuSignFields = dal.MyConnection();
return(docf);
}
private void btnSubmit_Click(object sender, EventArgs e)
{
Patients patient = new Patients();
patient.PatientID = txtPatientID.Text;
patient.FirstName = txtFirstName.Text;
patient.LastName = txtLastName.Text;
patient.CreditCardNumber = txtCreditCard.Text;
patient.Expiry = txtExpiry.Text;
patient.DateOfRegistration = dtDate.Value;
string jsonPatient = JsonConvert.SerializeObject(patient);
IDataAccessLibrary sqlDAL = new SQLDAL();
sqlDAL.InsertData(jsonPatient);
}
public String UpdMobileVerify(string strMobileno)
{
string strRetVal = "";
SQLDAL objSql = new SQLDAL();
MySqlCommand objCmd = new MySqlCommand();
MySqlDataAdapter objda = new MySqlDataAdapter();
try
{
objCmd = objSql.CreateCommand(objCmd, CommandType.StoredProcedure, "signupSp");
objSql.AddParameter(objCmd, "pAction", MySqlDbType.VarChar, ParameterDirection.Input, 50, "updmobileotp");
objSql.AddParameter(objCmd, "psignupId", MySqlDbType.Int32, ParameterDirection.Input, 50, null);
objSql.AddParameter(objCmd, "pfullName", MySqlDbType.VarChar, ParameterDirection.Input, 200, null);
objSql.AddParameter(objCmd, "peMail", MySqlDbType.VarChar, ParameterDirection.Input, 25, null);
objSql.AddParameter(objCmd, "pmobileNo", MySqlDbType.VarChar, ParameterDirection.Input, 50, strMobileno);
objSql.AddParameter(objCmd, "ppwd", MySqlDbType.VarChar, ParameterDirection.Input, 200, null);
objSql.AddParameter(objCmd, "pcpwd", MySqlDbType.VarChar, ParameterDirection.Input, 25, null);
objSql.AddParameter(objCmd, "pmobileOtp", MySqlDbType.VarChar, ParameterDirection.Input, 200, null);
objSql.AddParameter(objCmd, "pemailOtp", MySqlDbType.VarChar, ParameterDirection.Input, 25, null);
objSql.AddParameter(objCmd, "pcreatedBy", MySqlDbType.Int32, ParameterDirection.Input, 25, null);
strRetVal = objCmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
//CreateErrorlog.WriteError(ex.Message.ToString() + "\r\n" + ex.StackTrace);
}
finally
{
if (!(objCmd == null))
{
objCmd.Dispose();
if (objSql != null)
{
objSql.Dispose();
}
if (objda != null)
{
objda.Dispose();
}
}
}
return(strRetVal);
}
public DataTable DtGetAllSignUp()
{
DataTable dtVendorInfo = new DataTable();
SQLDAL objSql = new SQLDAL();
MySqlCommand objCmd = new MySqlCommand();
MySqlDataAdapter objda = new MySqlDataAdapter();
try
{
objCmd = objSql.CreateCommand(objCmd, CommandType.StoredProcedure, "signupSp");
objSql.AddParameter(objCmd, "pAction", MySqlDbType.VarChar, ParameterDirection.Input, 20, "selectverifiedsignin");
objSql.AddParameter(objCmd, "psignupId", MySqlDbType.Int32, ParameterDirection.Input, 50, null);
objSql.AddParameter(objCmd, "pfullName", MySqlDbType.VarChar, ParameterDirection.Input, 200, null);
objSql.AddParameter(objCmd, "peMail", MySqlDbType.VarChar, ParameterDirection.Input, 25, null);
objSql.AddParameter(objCmd, "pmobileNo", MySqlDbType.VarChar, ParameterDirection.Input, 50, null);
objSql.AddParameter(objCmd, "ppwd", MySqlDbType.VarChar, ParameterDirection.Input, 200, null);
objSql.AddParameter(objCmd, "pcpwd", MySqlDbType.VarChar, ParameterDirection.Input, 25, null);
objSql.AddParameter(objCmd, "pmobileOtp", MySqlDbType.VarChar, ParameterDirection.Input, 200, null);
objSql.AddParameter(objCmd, "pemailOtp", MySqlDbType.VarChar, ParameterDirection.Input, 25, null);
objSql.AddParameter(objCmd, "pcreatedBy", MySqlDbType.Int32, ParameterDirection.Input, 25, null);
objda.SelectCommand = objCmd;
objda.Fill(dtVendorInfo);
}
catch (Exception ex)
{
}
finally
{
if (!(objCmd == null))
{
objCmd.Dispose();
if (objSql != null)
{
objSql.Dispose();
}
if (objda != null)
{
objda.Dispose();
}
}
}
return(dtVendorInfo);
}
public void Test1()
{
var resolver = new Resolver();
//var dal = resolver.Resolve<IInfraDal>();
IInfraDal dal = new SQLDAL();
//IUserDal u = resolver.Resolve<IUserDal>();
IDocumentDAL u = resolver.Resolve <IDocumentDAL>();
//UserRequest use = new UserRequest() { UserID = "*****@*****.**", UserName = "******" };
u.CreateDocument(new Document()
{
UserID = "*****@*****.**", ImageURL = "k.png", DocumentName = "image"
});
//_paramConverter = new DBParameterConverter(dal);
//var con = dal.Connect("Server=(localdb)\\MSSQLLocalDB;Database=Images;" +
// "Trusted_Connection=True;");
////IInfraDal dal = new SQLDAL();
////IDBParameter p = resolver.Resolve<IDBParameter>();
////p.ParameterName = "UserID";
////p.Value = "*****@*****.**";
////IDBParameter p1 = resolver.Resolve<IDBParameter>();
////p1.ParameterName = "UserName";
////p1.Value = "rachel";
////UserRequest u = new UserRequest() { UserID = "*****@*****.**", UserName = "******" };
//DocumentRequset d = new DocumentRequset() { DocumentName = "new doc", ImageURL = "k.png", UserID = "*****@*****.**" };
//var p = _paramConverter.ConvertToParameter(d, "UserID");
//var p1 = _paramConverter.ConvertToParameter(d, "ImageURL");
//var p2 = _paramConverter.ConvertToParameter(d, "DocumentName");
//dal.ExecSPQuery("CreateDocument", con, p, p1, p2);
//var dataset = dal.ExecSPQuery("GetUsers", con);
//Console.WriteLine(dataset.Tables[0].Rows.Count);
//foreach (DataRow row in dataset.Tables[0].Rows)
//{
// Console.WriteLine("{0},{1}", row[0], row[1]);
//}
}
public List <SignUp> ListAllSignUp()
{
List <SignUp> lst = new List <SignUp>();
DataTable dt = new DataTable();
SQLDAL objSql = new SQLDAL();
MySqlCommand objCmd = new MySqlCommand();
MySqlDataAdapter objda = new MySqlDataAdapter();
try
{
conn.Open();
objCmd = objSql.CreateCommand(objCmd, CommandType.StoredProcedure, "signupSp");
objSql.AddParameter(objCmd, "pAction", MySqlDbType.VarChar, ParameterDirection.Input, 50, "selectsignin");
objSql.AddParameter(objCmd, "psignupId", MySqlDbType.Int32, ParameterDirection.Input, 50, null);
objSql.AddParameter(objCmd, "pfullName", MySqlDbType.VarChar, ParameterDirection.Input, 200, null);
objSql.AddParameter(objCmd, "peMail", MySqlDbType.VarChar, ParameterDirection.Input, 25, null);
objSql.AddParameter(objCmd, "pmobileNo", MySqlDbType.VarChar, ParameterDirection.Input, 50, null);
objSql.AddParameter(objCmd, "ppwd", MySqlDbType.VarChar, ParameterDirection.Input, 200, null);
objSql.AddParameter(objCmd, "pcpwd", MySqlDbType.VarChar, ParameterDirection.Input, 25, null);
objSql.AddParameter(objCmd, "pmobileOtp", MySqlDbType.VarChar, ParameterDirection.Input, 200, null);
objSql.AddParameter(objCmd, "pemailOtp", MySqlDbType.VarChar, ParameterDirection.Input, 25, null);
objSql.AddParameter(objCmd, "pcreatedBy", MySqlDbType.Int32, ParameterDirection.Input, 25, null);
objda.SelectCommand = objCmd;
objda.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
lst.Add(new SignUp
{
SignUpUserId = Convert.ToInt32(dr["signupUserId"]),
fullName = Convert.ToString(dr["fullName"]),
email = Convert.ToString(dr["eMail"]),
mobileNo = Convert.ToString(dr["mobileNo"]),
pwd = Convert.ToString(dr["pwd"]),
cpwd = Convert.ToString(dr["cpwd"]),
mobileOtp = Convert.ToString(dr["mobileOtp"]),
emailOtp = Convert.ToString(dr["emailOtp"]),
mobileverify = Convert.ToString(dr["mobileVerify"]),
emailverify = Convert.ToString(dr["emailVerify"])
});
}
}
catch (Exception ex)
{
return(lst);
}
finally
{
if (!(objCmd == null))
{
objCmd.Dispose();
if (objSql != null)
{
objSql.Dispose();
}
if (objda != null)
{
objda.Dispose();
}
}
}
return(lst);
}
